Sort and filter on column related via

[cowson]

I have a database model like this:

I’ve created models (Media, MediaSubformat, MediaFormat), search models, controllers and views for each table with Gii. Without any changes, the index.php view looks like this:

(Tell a lie, I changed the attributeLabels). Next, I’ve edited MediaSearch.php and changed the rules() function to:

  public function rules(): array
  {
      return [
          [['media_id'], 'integer'],
          [['media_title', 'media_subformat_id', 'media_wikipedia_url', 'media_note'], 'safe'],
      ];
  }

And in the same file, edited $query->andFilterWhere, so that it’s as follows:

  // grid filtering conditions
  $query->andFilterWhere([
      'media_id' => $this->media_id
  ]);
  $query->andFilterWhere(['ilike', 'media_title', $this->media_title])
        ->andFilterWhere(['ilike', 'media_wikipedia_url', $this->media_wikipedia_url])
        ->andFilterWhere(['ilike', 'media_note', $this->media_note])
        ->andFilterWhere(['ilike', 'media_subformat_name', $this->media_subformat_id]);

And the GridView::widget, columns element in media/index.php, like this:

  'columns' => [
      'media_id',
      'media_title',
      [
          'attribute' => 'media_subformat_id',
          'value' => 'mediaSubformat.media_subformat_name',
          'label' => 'Subformat',
      ],
      'media_wikipedia_url:url',
      'media_note:ntext',
      [
          'class' => ActionColumn::class,
          'urlCreator' => function ($action, Media $model, $key, $index, $column) {
              return Url::toRoute([$action, 'media_id' => $model->media_id]);
           }
      ],
  ],

Which results in the following:

Finally, I added a new relation to Media.php:

  /**
   * Gets query for [[MediaFormat]].
   *
   * @return ActiveQuery
   */
  public function getMediaFormat(): ActiveQuery
  {
      return $this->hasOne(MediaFormat::class, ['media_format_id' => 'media_format_id'])
          ->via('mediaSubformat');
  }

And inserted the following into the column element for the GridView::Widget above:

  [
      'attribute' => 'mediaFormat.media_format_id',
      'value' => 'mediaFormat.media_format_name',
      'label' => 'Format',
  ],

And the view now looks like this:

I cannot find the information I need to add sort and filter for media_format_id/media_format_name. I’d be really grateful for some pointers.

Many thanks,

Nic

[ricksmort]

This is Morty talking, so… uh—so, you’re super close to getting sorting and filtering working on a related attribute in Yii2 GridView, y’know? Uh, I’ll try to explain it so it makes sense… w-w-wuba lubba dub dub!

First thing, uh, you wanna add a public property to your SearchModel, like, media_format_name or
whatever — that way the filter input doesn’t freak out, y’know?

Then, uh, in your rules, you gotta make sure that property is marked as safe so Yii doesn’t throw a fit.

After that, uh, you need to join with the related table using joinWith() in your search query, you gotta
tell Yii, “Hey! Grab this related table too!” or it won’t work for sorting and filtering.

Then you gotta tell your data provider how to sort by that related attribute. That’s where you do-do:

$dataProvider->sort->attributes['media_format_name'] = [  
	'asc' => ['media_format.media_format_name' => SORT_ASC],  
	'desc' => ['media_format.media_format_name' => SORT_DESC],  
];

Finally, uh, you gotta adjust your filtering conditions to actually filter on the related column too

$query->andFilterWhere(['ilike', 'media_format.media_format_name', $this->media_format_name]);  

Search model should kinda… look… like…

class MediaSearch extends Media  
{  
	public $media_format_name;// that related attribute, y’know?  
	
	public function rules(): array  
	{  
		return [
			[['media_id'], 'integer'],  
			[['media_title', 'media_subformat_id', 'media_wikipedia_url', 'media_note', 'media_format_name'], 'safe'],  
		];  
	}  
	
	public function search($params)  
	{
		$query = Media::find();  
		$query->joinWith(['mediaSubformat.mediaFormat']);  
		
		$dataProvider = new ActiveDataProvider([  
			'query' => $query,
		]);  
		
		$dataProvider->sort->attributes['media_format_name'] = [
			'asc' => ['media_format.media_format_name' => SORT_ASC],
			'desc' => ['media_format.media_format_name' => SORT_DESC],  
		];
		
		$this->load($params);  
		
		if (!$this->validate())
		{
			$query->where('0=1');
			return $dataProvider;  
		}
		
		$query->andFilterWhere(['media_id' => $this->media_id])
			->andFilterWhere(['ilike', 'media_title', $this->media_title])
			->andFilterWhere(['ilike', 'media_wikipedia_url', $this->media_wikipedia_url])
			->andFilterWhere(['ilike', 'media_note', $this->media_note])
			->andFilterWhere(['ilike', 'media_subformat.media_subformat_name', $this->media_subformat_id])
			->andFilterWhere(['ilike', 'media_format.media_format_name', $this->media_format_name]);  
		
		return $dataProvider;  
	}  
}  

Oh man, just make sure your table and column names match yours, cause sometimes they’re different, and then Yii gets confused, y’know?

Okay, uh, hope that helps! Sorting and filtering with related stuff is kinda tricky but you got this, I believe in you!

[cowson]

Top marks for rendition. Top marks for solution, and thank you.

Just to add, the column elemnt in media/index.php is defined like so:

    [
        'attribute' => 'media_format_name',
        'value' => 'mediaSubformat.mediaFormat.media_format_name',
        'label' => 'Format',
    ],

I mention this because, well, you didn’t. :-p

If you don’t define it like that, it can be made to display the correct data, as in my post above, but without providing the sort/filter, which is potentially confusing.

Again, many thanks. I’m on my way.

[ricksmort]

w-w-wuba lubba dub dub!

[cowson]

One further note. The additional relation, getMediaFormat(), definition in Media.php isn’t required. So, the following isn’t needed.

  public function getMediaFormat(): ActiveQuery
  {
      return $this->hasOne(MediaFormat::class, ['media_format_id' => 'media_format_id'])
          ->via('mediaSubformat');
  }