One thing you have to note is:
In the ActiveRecord of Yii 2, the related models are always fetched with a separated query.
For instance:
$users = User::find()->with('posts')->all();
The code above will result in 2 queries:
select * from user;
select * from post where id in ( ... );
-- ( ... ) refers to the user ids fetched in the 1st query.
The 1st query is used to populate the array of main models, while the 2nd query is used to populate the related models. The result set of the 1st query is not used for the related models.
This is also true when you use "joinWith".
$users = User::find()
->select(['user.*', 'post.*'])
->innerJoinWith('posts')
->where(['like', 'post.title', 'yii'])
->all();
The code above will result in this:
select user.*, post.* from user inner join post on post.user_id = user.id
where post.title like '%yii%';
select * from post where id in ( ... );
You may select a column of the joined table in the first query, but note, it is not used to populate the related models. The related models are always fetched with the 2nd query.
As a consequence, while you can select all the users that have at least one post with the title having ‘yii’, but you can not exclude the posts that don’t have ‘yii’ in their title.
In order to filter the related models, you can adjust the relational condition on the fly as @Fabrizio has shown in a previous post.
$users = User::find()
->innerJoinWith([
'posts' => function($q) {
$q->andWhere(['like', 'post.title', 'yii']);
},
])
->all();
This condition will take effect not only for the 1st query but also for the 2nd query, and you’ll get the expected result.
select * from user inner join post on post.user_id = user.id
and post.title like '%yii%';
select * from post where id in ( ... ) and post.title like '%yii%';
