What is the difference between $with and $joinWith in Yii2 and when to use them?

Question

In the API documentation it is specified that

• $joinWith - A list of relations that this query should be joined with
• $with - A list of relations that this query should be performed with

What is the difference between these ActiveQuery property and under what situation should we use $joinWith and $with?

Answer 1

joinWith uses JOIN to include the relations in the original query while with does not.

To illustrate further, consider a class Post with a relation comments as follows:

class Post extends \yii\db\ActiveRecord {     ...     public function getComments() {         return $this->hasMany(Comment::className(), ['post_id' => 'id']);     } } 

Using with the code below:

$post = Post::find()->with('comments'); 

results in the following sql queries:

SELECT `post`.* FROM `post`; SELECT `comment`.* FROM `comment` WHERE post_id IN (...) 

Whereas the joinWith code below:

$post = Post::find()->joinWith('comments', true) 

results in the queries:

SELECT `post`.* FROM post LEFT JOIN `comment` comments ON post.`id` = comments.`post_id`; SELECT `comment`.* FROM `comment` WHERE post_id IN (...); 

As a result, when using joinWith you may order by/filter/group by the relation. You may have to disambiguate the column names yourself.

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