Filter relation fields

Yii 2.0 General Discussions
1

I am filtering using the _search partial view, in which i have a dropdown ‘office’ field.

So add the where clause via




$query->andFilterWhere(['=', 'property.office_id', $params['SaleSearch']['office']]);

where $params[‘SaleSearch’][‘office’] contains a simple id like ‘2’ and property table has a field ‘office_id’, contains ‘2’.

I’ve added ‘property.office_id’ to the safe rules and the query is joining via




$query = sale::find()->with([

            'property',

        ]);

What am i missing, as i’m getting the error




SQLSTATE[42S22]: Column not found: 1054 Unknown column 'property.office_id' in 'where clause'

The SQL being executed was: SELECT COUNT(*) FROM `sale` WHERE `property`.`office_id` = '2'

Error Info: Array

(

    [0] => 42S22

    [1] => 1054

    [2] => Unknown column 'property.office_id' in 'where clause'
2

Change


$query = Sale::find()->joinWith(['property']);

[size=2][/size]


$query->andFilterWhere(['=', 'property.office_id', $params['SaleSearch']['office']]);

Replace [color="#008800"][size=“2”]‘property.office_id’ [/size][/color]with[color="#008800"] ‘[/color]property-tablename.office_id’

3

whats the difference between “with” and “joinWith”, because i only put the with([‘property’]) in my initial post, but in fact there are other models in there, and using “joinWith” as you suggest breaks those others?

4

You have to learn how the relational queries work in Yii.

http://www.yiiframework.com/doc-2.0/guide-db-active-record.html#lazy-eager-loading

http://www.yiiframework.com/doc-2.0/guide-db-active-record.html#joining-with-relations

The key point is that Yii uses separated sqls for the main model and the related models.

http://www.yiiframework.com/wiki/834/relational-query-lazy-loadnig-and-eager-loading-in-yii-2-0/

5

thanks softark - this is whats confusing me - if u recall, u helped me getting this tricky situation solved: http://www.yiiframework.com/forum/index.php/topic/70785-how-to-show-a-hasmany-relation-in-a-grid/page__p__299211__fromsearch__1#entry299211

So i’m now trying to incorporate the above related field filter, into that same functionality.

i will read and come back with more questions i suspect :)