Through Relation Perfomance

Let’s look at example from official guide

Another nice relation type, moreover you can build it recursively. Group has users through roles. Group has comments through users, through roles. Very flexible and convenient.

But does anybody look inside built queries. Yii will build query like this. Even no, at first let’s imagine how I will build such query.


FROM role

    JOIN user ON = role.user_id

    JOIN comment ON = comment.user_id

WHERE  role.group_id = 123

And now let’s see what we get with yii

SELECT comment.*

FROM comment

    LEFT JOIN user ON = comment.user_id

    LEFT JOIN role ON role.user_id =

WHERE  role.group_id = 2870

It happens because yii build query recursively go down from first relation. A few WHY from my side.

  • why LEFT join

  • I expected to see everything viceversa

  • If user can belong to few roles – final result won’t be unique, i.e. we need to add DISTINCT. Thought Yii will filter it by themself.

I made some investigation and add EXPLAIN data here

But shortly Mysql is smart enough to start from role table in both cases. Take Five. I think in most cases this will work. And you’ll get almost the same performance.

But some issues is still relevant

  • why LEFT join

  • is it correct to get unique data using yii

  • does join server optimization will work for all supported db servers

Thoughts ?