[FIXED] "andFilterWhere" work proper with "joinWith()" but not with "with()" in yii2

Issue

I am working in yii2.

There are employee and company table employee contains company_id.

I have a filter search running properly If I use joinWith()

  $query = Employee::find();
  $query->joinWith(['company']);

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


    if (!($this->load($params) && $this->validate())) {
        return $dataProvider;
    }

  //and below is the filterwhere
 $query->andFilterWhere(['like', 'company.name', $this->company_id]);

But issue came when I make a query using with()

$query = Employee::find()->with(['company']);

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


    if (!($this->load($params) && $this->validate())) {
        return $dataProvider;
    }

//when query contain with() then this filter is not working.
$query->andFilterWhere(['like', 'company.name', $this->company_id]);

This gives error when I use with()

Database Exception – yii\db\Exception
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'company.name' in 'where clause'
The SQL being executed was: SELECT COUNT(*) FROM `employee` WHERE `company`.`name` LIKE '%1%'

Here is the relation in employee with company:

public function getCompany(){
    return $this->hasOne(Company::className(),  ['id'=> 'company_id']);
}

Can anyone help me or guide me how could I filter data properly using with() in a query? Thanks.

Solution

You can't swap joinWith() and with() methods when you need to filter by the column from the related table. That's because these methods does completely different things.

Methods like joinWith() and join() actually modifies the query to add the "JOIN" part to the SQL query. The with in joinWith allows you to specify the joined table by the relation definition in the model. The eager loading in joinWith is only side effect and you can even turn that off by passing false as second parameter.

When you do:

Employee::find()->joinWith(['company'])->all();

The query that is run looks like:

SELECT * FROM employee LEFT JOIN company ON (...)

On the other side the method with() doesn't modify the query itself. It only forces the eager loading of related models. In reality the second query is used for preloading the related records. When you do:

Employee::find()->with(['company'])->all();

It actually runs queries like these:

SELECT * FROM employee;

SELECT * FROM company WHERE id IN (...company ids selected in first query...);

So when you try to do:

$query = Employee::find()
    ->with(['company'])
    ->andFilterWhere(['like', 'company.name', $this->company_id])
    ->all();

The generated query is

SELECT * FROM employee WHERE company.name LIKE ...

Answered By - Michal Hynčica