Filter setup for related model in GridView

Question

I am trying to setup the filter for related model in Yii2's GridView widget, but I am keep getting the error like the filter value must be an integer.

I have followed this question. Now, I have a two models Services.php and ServiceCharge.php.

In ServiceCharge.php the relation is setup like:

public function getServiceName()
    {
        return $this->hasOne(Services::className(),['id'=>'service_name']);
    }

In the ServiceChargeSearch.php the code is like this:

<?php

namespace app\models;

use Yii;
use yii\base\Model;
use yii\data\ActiveDataProvider;
use app\models\ServiceCharges;

/**
 * ServiceChargesSearch represents the model behind the search form about `app\models\ServiceCharges`.
 */
class ServiceChargesSearch extends ServiceCharges
{
    /**
     * @inheritdoc
     */
    public function attributes()
    {
        // add related fields to searchable attributes
      return array_merge(parent::attributes(), ['serviceName.services']);

    }
    public function rules()
    {
        return [
            [['id'], 'integer'],
            [['charges_cash', 'charges_cashless'], 'number'],
            [['id', 'serviceName.services', 'room_category'], 'safe'],
        ];
    }

    /**
     * @inheritdoc
     */
    public function scenarios()
    {
        // bypass scenarios() implementation in the parent class
        return Model::scenarios();
    }

    /**
     * Creates data provider instance with search query applied
     *
     * @param array $params
     *
     * @return ActiveDataProvider
     */
    public function search($params)
    {
        $query = ServiceCharges::find();

        $dataProvider = new ActiveDataProvider([
            'query' => $query,
        ]);
        $dataProvider->sort->attributes['serviceName.services'] = [
        'asc' => ['serviceName.services' => SORT_ASC],
        'desc' => ['serviceName.services' => SORT_DESC],
        ];

$query->joinWith(['serviceName']); 

        $this->load($params);

        if (!$this->validate()) {
            // uncomment the following line if you do not want to any records when validation fails
            // $query->where('0=1');
            return $dataProvider;
        }

        $query->andFilterWhere([
            'id' => $this->id,
           // 'service_name' => $this->service_name,
            'room_category' => $this->room_category,
            'charges_cash' => $this->charges_cash,
            'charges_cashless' => $this->charges_cashless,
        ])
      ->andFilterWhere(['LIKE', 'serviceName.services', $this->getAttribute('serviceName.services')]);

        return $dataProvider;
    }
}

and in my Gridview it is setup like this:

[
                'attribute'=>'service_name',
                'value'=>'serviceName.services',

            ],

Which is showing the services name from the related model correctly.

I am not able to see what I am doing wrong, but the filter field for the attribute for service is not showing at all.

Answer 1

Actually it is much simpler than it seems.

1. add the column_name to safe attribute. Note: this should be relation Name

2. add the join with query - like - $query->joinWith(['serviceName','roomCategory']);

3. add the filter condition like:

   ->andFilterWhere(['like', 'services.services', $this->service_name])
   ->andFilterWhere(['like', 'room_category.room_category', $this->room_category]);
   

4. if like to add sorting add the code like:

   $dataProvider->sort->attributes['service_name'] = [
       'asc'  => ['services.services' => SORT_ASC],
       'desc' => ['services.services' => SORT_DESC],
   ];
   $dataProvider->sort->attributes['room_category'] = [
       'asc'  => ['room_category.room_category' => SORT_ASC],
       'desc' => ['room_category.room_category' => SORT_DESC],
   ];
   

5 you should also set the relation name say public $roomCategory

That's it. Both sorting and filtering for related table works perfectly.

Note: Remove default validation like integer for related column and default filtering generated by gii otherwise it will generate an error.

Update on Latest version:

• Adding Public $attribute is not needed.
• Adding safe attribute for relation is also not needed.
• but the attribute in your current model, which you want filter is to added to safe attribute that is a must.
• and most importantly in your gridview, the related attribute has to be in closure format.

that is example

[
'attribute=>'attribute_name',
'value=function($data){
     return $data->relationname->related_table_attribute_name
}
],

remember it you are using relation_name.related_table_attribute_name filter somehow doesn't work for me.

Answer 2

There is a fairly comprehensive set of instructions on the Yii Framework website. The only thing to note is that the search model complains about the following lines, but everything appears to work as intended without them:

$this->addCondition(...);

For a model, PaymentEvent (table: subs_payment_event), which has a currency_id field linked to model Currency, this is the complete set of additional code (using the Basic template):

In the main model, PaymentEvent.php:

public function getCurrencyName()
{
    return $this->currency->name;
}

In the search model, PaymentEventSearch.php:

public $currencyName;

In its rules:

[['currencyName'], 'safe'],

In the attributes of its setSort statement, include:

'currencyName' => [
    'asc' => ['subs_currency.name' => SORT_ASC],
    'desc' => ['subs_currency.name' => SORT_DESC],
    'label' => 'Currency'
],

Before the grid filtering conditions:

$query->joinWith(['currency' => function ($q) {
        $q->where('subs_currency.name LIKE "%' . $this->currencyName . '%"');
    }]);

Finally, in the GridView columns array in the view (including my usual link across to the related model records):

[
    'attribute' => 'currencyName',
    'label' => 'Currency',
    'format' => 'raw',
    'value' => function ($data) {
            return Html::a($data->currency->name, ['/currency/' . $data->currency_id]);
        },
],