Well, this design flaw (we perfer to call it a compromise) was made on purpose. Pros we’ve got:
Much simpler code for populating related models. Less mistakes. In 1.1 it was too complicated to fix/enhance. Even simple changes were hard.
Ability to use cross-database queries including noSQL.
Simpler API for end user.
It’s not possible to get wrong results from AR the way softark explained (by using togher and limit / offset).
Of course there are cons:
One more query. Usually it has acceptable performance.
Length of IDs for IN() could be too big. There are limitations on DB side. Counter-argument for that is getting more than a hundred AR models for a single request is already a very bad idea from memory footprint standpoint.
No, you don’t need to. You can use exactly the same query to get the count as well as the data.
Yii (not you, but the framework code) just has to change the “select” part of the 1st query to construct the sql to get the count, as Yii 1.1 did for the CDbCriteria. As a result, count of Yii 2.0 may be lighter and faster than that of Yii 1.1, since you don’t need to include the sql parts for retrieving the related models that are reserved for the 2nd and later queries.
I feel that a single query eager loading could be a good option for a simple HAS_ONE (or BELONGS_TO) relation. I would appreciate it if it would be implemented. But again, thinking about the nested relations, it looks not that simple.
Probably the most difficult thing should be the management of table aliases when we try to implement single query eager loading.
For the ‘has-one’ relations you can use following yii2tech/ar-eagerjoin extension. It will populate joined relation without extra SQL query being executed.
