Eager loading and "WHERE" clause

What’s the difference between




$customers = Customer::find()->with([

    'country',

    'orders' => function ($query) {

        $query->andWhere(['status' => Order::STATUS_ACTIVE]);

    },

])->all();



and




$customers = Customer::find()->with([

    'country',

    'orders'

])

->andWhere(['orders.status' => Order::STATUS_ACTIVE])

->all();



Is there any performance difference?

Did you try it yourself?

The latter will not work, saying that “column ‘orders.status’ not found”. In order to filter by a column in the related table, you had to use “joinWith” instead of “with”.

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

So the latter code should be:




$customers = Customer::find()->joinWith([

    'country',

    'orders'

])

->where(['order.status' => Order::STATUS_ACTIVE])

->all();



Note that the table prefix should be ‘order’ instead of ‘orders’.

The difference between the two lies mostly in the total number of returned customers. The former will return all the customers, including ones that have no active orders. The latter will return only the customers who have at least one active order.

And there are no ‘inactive’ orders in the result of the former, because you have modified the definition of the relation to include only the ‘active’ orders. But there are not only ‘active’ orders but also ‘inactive’ orders in the result of the latter, because the definition of the relation is not changed.

Note that the where clause in the former is for the related model, while it is for the main model in the latter.

A key concept: the query for the main model and those for the related models are clearly separated in Yii 2.

http://www.yiiframework.com/wiki/780/drills-search-by-a-has_many-relation-in-yii-2-0

I understand, thank you :)