Search in Related Model using GridView Filter Fiel

Question

Question: Let's Say i have two tables,

Table1            Table2
-authorId         -username

As of now my sql query on search model looks like this,

->andFilterWhere(['"Table1"."authorId"' => $this->authorName]);

It only helps to search and filter using the authorID.

Expected Result I would like to search based on the authorname instead of authorId.

---

I faced similar difficulty in reflecting data in view but i was able to fix it with following getter function in base model.

public function getAuthorName() {
        return $this->author->username;
    }

Thanks in Advance,

Answer 1

First, add a custom attribute to your search model

public $username

define a relation to your author table inside the model

public function getAuthor() {
        return $this->hasOne(Author::className(),['id'=>'authorId']);
    }

in your gridview replace the authorId column with

`author.username`

and use the following to compare

$query->andFilterWhere(['like', '{{%Table2}}.username',$this->username]);

and yes make sure you join you searchmodel query with the author table in the start of search function

->joinWith('author')

Answer 2

Assuming authorId is a FK to table2, and you have previoulsy joined table2 to table1, you can do the following:

->andFilterWhere(['table2.username' => $this->authorName]);