Using joinWith() generates extra queries

Yii 2.0 General Discussions
1

Profile model:

public function getUserAddress()
{
    return $this->hasOne(UserAddress::className(), ['id' => 'user_address_id']);
}

public static function findByUsername($username)
{
    return static::find()
        ->where(['username' => $username])
        ->joinWith(['userAddress'])
        ->one();
}

This results in the following two queries:

SELECT * FROM `profile`
LEFT JOIN `user_address` ON `user_address_id` = `user_address`.`id` 
WHERE (`username`='gstar')

SELECT * FROM `user_address` WHERE `id`=9

How can I ensure that the second query is not run separately and instead the columns are returned only in the main query?

2

You can not when using AR.

3

->joinWith(['userAddress'], false) will suppress the 2nd query, but by doing so you’ll miss the columns in ‘userAddress’.
Yii’s Active Record always executes a separated 2nd query for the related table by design.

4

I can understand this behaviour for when you want to retrieve multiple records, as mentioned in the guide: https://www.yiiframework.com/doc/guide/2.0/en/db-active-record#joining-with-relations

But when you just want to do a one-to-one join it doesn’t make sense to have this behaviour. I might as well not use joinWith() and just call the relation manually.

I remember in Yii1 there was an option called together which would retrieve all the data in one query. So it IS possible to do, but I’m not sure why it isn’t supported in Yii2.

5

Yeah, I also understand what you want to say, since it IS possible and would be more effective for a simple HAS_ONE relation to get both main model and related model in one query. It has been and will continue to be a controversial design decision. But I second the design choice of the developers, because it’s more simple and effective as a whole.