limit hasMany records

is there any way to limit hasMany rows per record?

for example, if i do:

Post::find()->with( ['comments'] )->where( ['id' => '1,2,3,4,5'] )->all();

and i have a getComments relation defined in Post like this:

$this->hasMany( Comment::className(), ['postId' => 'id'] )->limit( 10 );

then the original query only returns 10 comments TOTAL instead of 10 comments per post. is there proper way to limit hasMany rows per record? if hasMany simply wasn’t designed to accommodate this then there are other ways to solve this. just wondering if hasMany can be correctly limited per record.

We can not use eager loading approach here, because "limit" will not be applied to individual records but to the whole record.

Post::find()->with( ['comments'] )->where( ['id' => '1,2,3,4,5'] )->all();

This will result in 2 SQLs.

[sql]

select * from post where id in (1,2,3,4,5);

select * from comments where postId in (1,2,3,4,5);

[/sql]

The first query is for main model "Post". It will populate 5 instances of "Post".

And the second one is for related model "Comment". Yii will loop through the result set and add the related models to the main model.

When you apply "limit" to the relation, then the second query will be modified to:

[sql]

select * from comments where post_id in (1,2,3,4,5) limit(10);

[/sql]

As you have already noticed, this is not what you are expecting.

I think the solution is to use the lazy loading approach.

$posts = Post::find()->where( ['id' => '1,2,3,4,5'] )->all();

foreach($posts as $post) {

    $comments = $post->comments;

}

I hope this will execute the following sqls:

[sql]

select * from post where id in (1,2,3,4,5);

select * from comments where postId = 1 limit(10);

select * from comments where postId = 2 limit(10);

select * from comments where postId = 3 limit(10);

select * from comments where postId = 4 limit(10);

select * from comments where postId = 5 limit(10);

[/sql]

Please check the following wiki for eager loading and lazy loading.

http://www.yiiframework.com/wiki/834/relational-query-lazy-loadnig-and-eager-loading-in-yii-2-0/

i found a query that can do exactly what i want. unfortunately Yii’s query builder cannot build this query so i am forced to build it with raw sql.

this query will pull the top 5 comments (ordered by time) from a list of posts (post ids are in an array called $postIds)

SELECT comment.*                                                                                        

FROM comment                                                                                            

WHERE (                                                                                                 

   SELECT COUNT(*)                                                                                      

   FROM comment AS c2                                                                                   

   WHERE c2.postId = comment.postId                                                                     

   AND c2.timestamp >= comment.timestamp                 

   ORDER BY c2.timestamp DESC ) <= 5

AND comment.postId IN ($postIds)                                                                        

ORDER BY comment.timestamp DESC