php - Filtering yii2 grid using a form with related model

Question

Am using a search model with a search form and i would like to filter a grid view based on the value entered in the form. The form field is of a related table

Am actually searching tblpritems and filtering its grid on the column pr_solicitation_id by entering supplier_id as in the tblprsuppliers

This is the model relationships In the tblpritems

public function getPrSolicitation()
{
return $this->hasOne(Tblprsolicitations::className(), ['pr_solicitation_id'   
 => 'pr_solicitation_id']); 
}

In the Tblprsolicitations model is related to tblprsuppliers by

public function getPRsuppliers()
 {
  return $this->hasOne(Tblprsuppliers::className(), ['pr_solicitaion_id'
  => 'pr_solicitaion_id']);
}

I have tried

This is my search form (for the tblpritems). This references the pr_solicitation_id field in the tblpritems table

 <?= $form->field($model, 'prSolicitation[pRsuppliers][supplier_id]')->textInput(['placeholder' => 'Enter supplier'])->label(false); ?>

But this does not filter the grid

This is also the grid search

    public function search($params)
{
    $query = Tblpritems::find();

    // add conditions that should always apply here

    $dataProvider = new ActiveDataProvider([
        'query' => $query,
    ]);

    $this->load($params);


    // grid filtering conditions
    $query->andFilterWhere([
        'PRlineID' => $this->PRlineID,
        'Quantity' => $this->Quantity,
        'Unit_Price' => $this->Unit_Price,
        'Extended_price' => $this->Extended_price,
        'Currency_ID' => $this->Currency_ID,
        'PRID' => $this->PRID,
        'pr_solicitation_id' => $this->pr_solicitation_id,  //This is what am using t filter the grid
        'date_item_received' => $this->date_item_received,
        'Quantity_received' => $this->Quantity_received,
        'Received_by' => $this->Received_by,
        'item_received_status' => $this->item_received_status,
    ]);

    $query->andFilterWhere(['like', 'Tracking_Code', $this->Tracking_Code])
        ->andFilterWhere(['like', 'Description', $this->Description])
        ->andFilterWhere(['like', 'Remarks_on_receipt', $this->Remarks_on_receipt]);

    return $dataProvider;
}

Why is it not working?

I have also tried

<?= $form->field($model, 'prSolicitation->pRsuppliers[supplier_id]')->textInput(['placeholder' => 'Enter supplier'])->label(false); ?>   

but this returns an error of only characters should be passed

Answer 1

You need to create a virtual attribute in your search model, mark it as safe in your search rules, then search with relations, add the filters in where statement and add the input in the form or grid.

In your search model add a virtual attribute:

public $supplier_id;

In your rules add:

[['supplier_id'], 'safe'],

or

[['supplier_id'], 'integer'],

In search method search with relations like this:

$query = Tblpritems::find();
$query->joinWith(['prSolicitation prSolicitation', 'prSolicitation.pRsuppliers pRsuppliers']);

And in your filters add:

$query->andFilterWhere([
    'PRlineID' => $this->PRlineID,
    'Quantity' => $this->Quantity,
    'Unit_Price' => $this->Unit_Price,
    'Extended_price' => $this->Extended_price,
    'Currency_ID' => $this->Currency_ID,
    'PRID' => $this->PRID,
    'pr_solicitation_id' => $this->pr_solicitation_id,  //This is what am using t filter the grid
    'date_item_received' => $this->date_item_received,
    'Quantity_received' => $this->Quantity_received,
    'Received_by' => $this->Received_by,
    'item_received_status' => $this->item_received_status,

    'prSolicitation.pRsuppliers.pr_supplier_id' => $this->supplier_id,

]);

In form:

<?= $form->field($model, 'supplier_id')->textInput(['placeholder' => 'Enter supplier'])->label(false); ?>

Also check this two links:

Yii 2.0: Displaying, Sorting and Filtering Model Relations on a GridView

and

Filter & Sort by calculated/related fields in GridView Yii 2.0