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.
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