Hi,
Having used Yii 1.1 for a long time (though just in very few projects), I haven’t tried Yii 2.0 yet, but I’m reading the Guide and I am astonished to find this:
Please tell me I’m missing something. Please tell me, at least, that this is just the default and there’s an option to change that behavior.
If that is not the case, and if I understand this correctly, it would be a disastrous choice that destroys ActiveRecord completely and leaves us with writing literal SQL queries by hand as the only reasonable option.
Are you telling me that, in order to eager loading a bunch of, let’s say, posts with their corresponding authors (say it’s a BELONGS_TO relation called authorUser to a model whose table is user), it will perform two queries roughly similar to this:
//first query, retrieves the primary records:
SELECT /*...fields...*/ FROM post WHERE ......;
//second query, retrieves the related records:
SELECT /*...fields...*/ FROM user WHERE post_id IN(1,13,32,.....) AND ....
Is that so? It can’t be. I must be missing something.
If it were so, it would be a complete disaster.
The first issue with this is that it does two queries instead of one. Multiplying by a factor of 2 the number of queries might be an insignificant issue. However, if you eager-load N relation at once, will it do 1+N queries, or will it still be 2 queries, one for the primary records and one for all the relations? If it’s the former, then the issue starts being relevant, even though it’s still a small limited multiplcation factor in the number of queries (but still totally avoidable).
The second, HUGE, issue is that this may result in insanely long queries. If, for example, you are eager-loading thousands of records, it means the second query would have an IN() condition with thousands of ids. I’m not even sure if that implies efficiency issues in retrieving the records, but what’s for sure is that a too-long query (in terms of number of characters) is an issue.
Also, if you think the issue of query length is relatively unimportant (but that’s not true) when you’re going to get a huge amount of records in the result anyway (and CPU-processing them to turn them into models), then consider this:
In Yii 1.1, you could use relations in a Criteria with with and together and use that criteria to count records.
For example (i’m writing examples without testing so I might leave something out, but the overall concept holds true and I have used and verified it several times):
// Yii 1.1:
// Get the total number of visible posts by visible users
$criteria = new CDbCriteria(array(
'with'=>array(
'authorUser'=>array(
'joinType'=>'INNER JOIN',
'on'=>'authorUser.visible>0'
)
),
'together'=>true,
'condition'=>'t.visible>0'
));
$count=Post::model()->count($criteria);
This would produce in Yii 1.1 a query roughly similar (or equivalent) to this:
SELECT COUNT(post.id)
FROM post AS t INNER JOIN user AS authorUser ON authorUser.id=t.id AND authorUser.visible>0
WHERE post.visible > 0
that is, an O(1)-length query for a O(1)-size result, which would execute as effeciently as it gets provided that the tables have the indexes set up properly.
Now that I think about it, I wonder whether that criteria can even be expressed in Yii 2.
Please, prove me wrong and let me see what’s the part I’ve missed, because if I got it right it would mean I have to immediately stop investing my time in learning Yii 2.