Using relations doubles my query

Yii 2.0 General Discussions
1

I’m using relation like this:


$query=TimezoneUser::find()->where(['user_id'=>Yii::$app->user->getId()])->joinWith(['relationTimezone'])->one();

TimezoneUser has relation:




    public function getRelationTimezone()

    {

        return $this->hasOne(Timezone::className(), ['id' => 'timezone_id']);

    }

I’m taking data like this


$query->relationTimezone->timezone

But when I look ad debugger there is 2 queries


SELECT `timezone_user`.* FROM `timezone_user` LEFT JOIN `timezone` ON `timezone_user`.`timezone_id` = `timezone`.`id` WHERE `user_id`=14

and


SELECT * FROM `timezone` WHERE `id`=413

Why 2 queries when I can get value "timezone" just buy execute one query using left join.

2

It’s by design.

Yii 2 will execute at least 2 queries when you include the relations using "with" or "joinWith". One for the main model and the other for the related models. In other word, the fetching of the main model and that of the related models is cleanly separated.

It’s very natural that you may wonder why. It looks (and is) less effective than the strategy used by Yii 1.1 that fetches both of them in a single query for a standard “has one” relation.

But this change in the design has introduced at least 2 good things:

  1. It enables the relation between a SQL database and a NON SQL database.

  2. It solves the complicated problem regarding "has many" relations.

As for the latter, please take a look at this wiki article:

http://www.yiiframework.com/wiki/527/relational-query-lazy-loading-and-eager-loading-with-and-together/

3

Thank you :)

Sometimes I get up to 100-200 queries because of relations :o

4

That’s too many.

Aren’t you fetching each instance of the models one by one (using “one()”) INSIDE the loop?

It means that when you deal with a model with 2 relations, then the total number of queries will be from 3 to 5.

Consider using "eager loading" approach in your app.

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

5

Well in foreach() I’m listing all elements but getting another query inside foreach() to get data.

For example in foreach() I’m listing categories and for every category I have another query that counts how many stories is in that category

6

Like this?




$categories = Category::find()->where( .... )->all();

foreach($categories as $category) {

    $stories = Story::find()->where(['category_id' => $category->id])->all();

    foreach($stories as $story) {

        echo $story->title;

    }

}

Isn’t it possible to do it like this?




$categories = Category::find()->with(['stories'])->where( .... )->all();

foreach($categories as $category) {

    // The following is not necessary any more

    // $stories = Story::find()->where(['category_id' => $category_id])->all();

    foreach($category->stories as $story) {

        echo $story->title;

    }

}
7

I’m using second way, but I have to take number of stories withing a category in second foreach() loop and that’s where I call another query

8

That’s exactly what “eager loading” is meant for.

Sorry if I’m wrong, but don’t you misunderstand that “with” and/or “joinWith” work only for “has one” relation? And did you consider using the relations of a related model … I mean, nested relations … ?

9

Ahaaa so "with" and "joinWith" are working only with "has one" relation. I used query builder to write my own query for this problem

10

Oh, sorry. I wanted to say that "with" and "joinWith" work also for "has many" relation, and probably you overlooked it …