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?

Shellishellie

Answer 1

Difference between with and joinWith

Using with method results the following SQL queries

$users = User::find()->with('userGroup');

SELECT * FROM `user`;
SELECT * FROM `userGroup` WHERE userId = ...

... while using joinWithwill result in this SQL query

$users = User::find()->joinWith('userGroup', true)

SELECT * FROM user LEFT JOIN `userGroup` userGroup ON user.`id` = userGroup.`userId`;

So I'am using joinWith when I need to filter or search data in the related tables.

Additional informations

The docu -> http://www.yiiframework.com/doc-2.0/guide-db-active-record.html#joining-with-relations will tell you this:

"When working with relational databases, a common task is to join multiple tables and apply various query conditions and parameters to the JOIN SQL statement. Instead of calling yii\db\ActiveQuery::join() explicitly to build up the JOIN query, you may reuse the existing relation definitions and call yii\db\ActiveQuery::joinWith() to achieve this goal."

Which means, you are able to handle joins, innerJoins, outerJoins and all the good related stuff in Yii2 by yourself now. Yii (not Yii2) only uses join instead without letting the user decide about type of join. Details about "Join's" -> its a SQL-Based thing. You can read about this here http://en.wikipedia.org/wiki/Join_(SQL)

Lanyard

joinWith still results in 2 queries... for reasons I don't quite understand... I have always understood "the fewer SQL queries the better". joinWith is therefore only useful if you are querying on a column in the related table, otherwise it's only a drawback. – Zootoxin

It's not necessary true that fewer SQL queries is always better. It depends on how well are indexed columns. The greatest difference between with and joinWith would be notable on huge datasets - e.g. joining millions of records between two tables is much more intensive operation that making simple subquery on indexed column (actually two simple queries). Thus using simpler with in such a case would be most likely a better choice. – Karinekariotta

@Zootoxin try to set eagerLoading to false. this must make it perform only one query instead of two. – Eightfold

Answer 2

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

Confucius

Answer 3

Please note that in addition to above awesome answers that helped me figure out how to use joinWith(), that whenever you want to use joinWith() and you have ambiguous column names, Yii / ActiveRecord automagically seems to pick a random column, instead of what you're usually expecting (the leftmost table). It is best to specify the leftmost table in the SELECT clause, by specifying something like $query->select("post.*"). I was getting ids from some inner tables and they were getting used like they were from the leftmost table, until I figured this out.

Another point to note is that you can specify an an alias for the joinwith relation, so you could say something like:

$post->find()
->joinWith(["user u"])
->where(["u.id"=>$requestedUser->id])
->select("post.*")
->orderBy(["u.created_at"=>SORT_DESC]);

Oceanus

Answer 4

joinWith

Yii2 code:

$query = MiniAppRisk::find()->joinWith(['country','text','riskType'])->orderBy(['risk_publish_date' => SORT_DESC]);
$dataProvider = new ActiveDataProvider([
            'query' => $query,
            'pagination' => [
                'pageSize' => 10,
                'page' =>0
            ],
        ]);

result sql1:

SELECT `fmm_text_risk_t`.risk_id FROM `fmm_text_risk_t` 
LEFT JOIN `fmm_text_risk_target_t` ON `fmm_text_risk_t`.`risk_id` = `fmm_text_risk_target_t`.`risk_id` 
LEFT JOIN `country` ON `fmm_text_risk_target_t`.`country_id` = `country`.`id` 
LEFT JOIN `fmm_text_t` ON `fmm_text_risk_t`.`text_id` = `fmm_text_t`.`text_id` 
LEFT JOIN `fmm_text_risk_type_relation_t` ON `fmm_text_risk_t`.`risk_id` = `fmm_text_risk_type_relation_t`.`risk_id` 
ORDER BY `risk_publish_date` DESC LIMIT 10

with

Yii2 code:

$query = MiniAppRisk::find()->with(['country','text','riskType'])->orderBy(['risk_publish_date' => SORT_DESC]);
$dataProvider = new ActiveDataProvider([
            'query' => $query,
            'pagination' => [
                'pageSize' => 10,
                'page' =>0
            ],
        ]);

result sql2

SELECT DISTINCT * FROM `fmm_text_risk_t` ORDER BY `risk_publish_date` DESC LIMIT 10 ;
SELECT * FROM `fmm_text_risk_target_t` WHERE `risk_id` IN (5563, 5562, 5561, 5560, 5559, 5558, 5557, 5556, 5555, 5554);
SELECT * FROM `country` WHERE `id` IN (7, 165, 1, 20);
SELECT * FROM `fmm_text_t` WHERE `text_id` IN (15272, 15271, 15270, 15269, 15268, 15267, 15266, 15265, 15264, 15263);
SELECT * FROM `fmm_text_risk_type_relation_t` WHERE `risk_id` IN (5563, 5562, 5561, 5560, 5559, 5558, 5557, 5556, 5555, 5554);
...more 

It seems that the SQL generated by joinWith is less, and the efficiency is higher. However, be cautious when using joinWith for pagination. When you examine their execution results, you will notice that the number of entries per page is less than 10. This is due to the mechanism of the left join. In the case of a one-to-many relationship, it may result in data duplication, and the 'LIMIT 10' applies to the entire SQL limit, exacerbating the issue.So got these 'risk_id':

27680
27680
27900
27900
27900
30099
30099
30301
30301
30489

To address this issue, you can do the following:

$query = MiniAppRisk::find()->joinWith(['country','text','riskType'])->distinct();

SELECT DISTINCT `fmm_text_risk_t`.risk_id FROM `fmm_text_risk_t` 
LEFT JOIN `fmm_text_risk_target_t` ON `fmm_text_risk_t`.`risk_id` = `fmm_text_risk_target_t`.`risk_id` 
LEFT JOIN `country` ON `fmm_text_risk_target_t`.`country_id` = `country`.`id` 
LEFT JOIN `fmm_text_t` ON `fmm_text_risk_t`.`text_id` = `fmm_text_t`.`text_id` 
LEFT JOIN `fmm_text_risk_type_relation_t` ON `fmm_text_risk_t`.`risk_id` = `fmm_text_risk_type_relation_t`.`risk_id` 
ORDER BY `risk_publish_date` DESC LIMIT 10

When using with limiting is applied only to the primary table, thus avoiding the aforementioned issue.

Floydflss