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