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

Question

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.

Try this: `->andFilterWhere(['like', 'table_name.name', $this->company_id]);` — Serghei Leonenco, Apr 10 '20 at 02:41
@SergheiLeonenco it's relation i have added here and table name is also "company" — always-a-learner, Apr 10 '20 at 03:33
i got it, but when you add `with()` it just add the join statement, so when you want to get a value from your joined table use a REAL TABLE NAME to get this value — Serghei Leonenco, Apr 10 '20 at 03:35
the error shows the same thing `Column not found: 1054 Unknown column 'company.name'`, add a real table name and issue will be resolved. — Serghei Leonenco, Apr 10 '20 at 03:37
Same question was discussed here: https://forum.yiiframework.com/t/how-to-include-relation-of-relation-in-find-model/84982 , you have to use `joinWith(['company']);` — Serghei Leonenco, Apr 10 '20 at 03:46
Try to edit this `->with(['company'])` to this: `->with('company')` — Serghei Leonenco, Apr 10 '20 at 03:51
@SergheiLeonenco query and filters are working if i use `joinWith()` but actually my client insisted to use `with()` and filter the data. so this is why i am searching here that how could i get the relations column search when i use `with()` — always-a-learner, Apr 10 '20 at 03:51
@SergheiLeonenco ` ->with('company')` tried this but same result — always-a-learner, Apr 10 '20 at 03:53
i got it, you need to Retrieving Data in Batches : https://www.yiiframework.com/doc/guide/2.0/en/db-active-record#data-in-batches — Serghei Leonenco, Apr 10 '20 at 03:53
@SergheiLeonenco am i need to add foreach instead of query ?? — always-a-learner, Apr 10 '20 at 03:54
@SergheiLeonenco I have edited the question here. and added the relation which i used in the employee for company — always-a-learner, Apr 10 '20 at 03:58
You have to load all data before to filter with `where` like this: `$query = Employee::find()->with('company')->all();` Try this, here is the link for explanation: https://www.yiiframework.com/wiki/834/relational-query-lazy-loading-and-eager-loading-in-yii-2-0 — Serghei Leonenco, Apr 10 '20 at 04:04
This `all()` give this error `Call to a member function andFilterWhere() on array` @SergheiLeonenco — always-a-learner, Apr 10 '20 at 04:08
Are you using this in GridView? if not add `->all()` at the end of the query. — Serghei Leonenco, Apr 10 '20 at 04:10
Here's explanation which should be used and actual query performed [Yii2 with() and Joinwith()](https://stackoverflow.com/a/25609523/3787519) — Insane Skull, Apr 10 '20 at 04:36
@InsaneSkull heyy Ankit i just watched your video.. small world.. is this impossible to filter search using "with()"? — always-a-learner, Apr 10 '20 at 04:44
Why you want to use only `with()`? It is possible to apply condition in `with()` but it works differently not as a filter for grid as i mentioned in my previous comment. — Insane Skull, Apr 10 '20 at 10:13
For example , `Model::find()->with(['relation' => function ($query) { $query->andFilterwhere(['field' => 'value']);}]);` — Insane Skull, Apr 10 '20 at 10:15

Michal Hynčica · Accepted Answer · 2020-04-11T16:26:56.730

3

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 ...

edited Apr 11 '20 at 16:26

answered Apr 10 '20 at 14:43

Michal Hynčica

5,038
1
12
24

the query which you gave here is also not working. It gives back the same error. I have tried to add `with()` in the `andFilterWhere` – always-a-learner Apr 22 '20 at 06:01
@always-a-learner I said in the first sentence that you can't swap `with()` and `joinWith()`. I've tried to explain how both of them are working so you can see why it is impossible to swap them. TLDR is that `with()` doesn't do a `JOIN` so you can't use columns from the other table to filter the results. – Michal Hynčica Apr 22 '20 at 06:11
yes sir I got that but the only thing which I don't understand is like the `with()` is getting the correct data on my gird view and like the same that data I want to filter then it not working. if data can be available for the view they whey not for the filter. – always-a-learner Apr 22 '20 at 07:48
1

@always-a-learner that's because the data for the grid are loaded in two separate queries. First the data for `Employee` models from table `employee` are selected. Then data for related `Company` models from `company` table are selected in the second independent query. The filter is applied on the first query where the columns from company table are not available. – Michal Hynčica Apr 22 '20 at 08:21
1

Technically you can load all models with `all()`. Then you can filter them by yourself with `array_filter()` or something similar. Finally you will use `yii\data\ArrayDataProvider` for grid. But this solution is much more complicated, slower and it will use more memory than `joinWith()`. – Michal Hynčica Apr 22 '20 at 08:22

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

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

1 Answers1

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