Let’s look at example from official guide
http://www.yiiframework.com/doc/guide/1.1/en/database.arr#relational-query-with-through
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.
SELECT *
FROM role
JOIN user ON user.id = role.user_id
JOIN comment ON user.id = 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 user.id = comment.user_id
LEFT JOIN role ON role.user_id = 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 http://radzserg.com/2013/06/14/yii-through-mysql-perfomance/
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 ?