Limiting laravel query using whereIn

Question

So i have a query to make but i don`k know how best to it. I have 2 class like below:

User.class

class User {
    function posts(){
        return $this->hasMany(Post::class, 'user_id');
    }
}

Post.class

class Post {
    function user(){
        return $this->belongsTo(User::class, 'user_id');
    }
}

and in my controller i want to get posts per User with a limit for each user NOT for all results. So here is what i have this in my controller:

function getPosts(Request $request){
    $user_ids = [1,2,3,4];

    $posts = Post::whereIn('user_id', $user_ids)->latest()->take(10)->get();
    return $posts;
}

So the above get will get me just 10 entries from all of them yet i want to get 10 for each user nomatter how many user IDs

check https://stackoverflow.com/questions/42124031/mysql-limiting-rows-per-wherein — Prafulla Kumar Sahu, May 08 '19 at 05:35
Thanks @PrafullaKumarSahu, is there a way of using a query builder for this, i am not that good in raw SQL — Flash, May 08 '19 at 16:18
I have just converted that question's solution to DB query, let me try to write a raw query for you , that we can convert to DB query after that. — Prafulla Kumar Sahu, May 08 '19 at 17:02

score 0 · Answer 1 · answered May 07 '19 at 14:16

0

You can simply limit the relationship with a sub-query:

User::with(['posts' => function($query) {
    return $query->limit(10);
}]);

answered May 07 '19 at 14:16

Dan

5,140
2
15
30

I would have done that but it will return me `User objects with posts` but i want it to return `Posts with User`. I am using Dingo API so i want to transform `Post objects` – Flash May 07 '19 at 14:20
Ohh, alright. Gotcha. I don't think the Query Builder can do that in one query. You may have to consider using MySQL for that. – Dan May 07 '19 at 14:26
Found something that might help you: https://stackoverflow.com/q/21760789 – Dan May 07 '19 at 14:28
Thanks buddy, will try and look at it but am not good with RAW queries – Flash May 07 '19 at 14:34
You can't put limits on eager loaded relationships like this. Say you have 10 users with 20 posts each (for a total of 200 posts). This query will get all 10 users, but it will only eager load 10 posts **total**, not 10 posts per user. – patricus May 07 '19 at 15:01
The limit and take query builder methods may not be used when constraining eager loads. https://laravel.com/docs/5.8/eloquent-relationships#constraining-eager-loads – Ahmed Aboud May 07 '19 at 15:03
Haven't tested it yet but it definitely looks promising to me! Cheers Ahmed! – Dan May 08 '19 at 06:50

score 0 · Answer 2 · answered May 08 '19 at 17:08

0

May be something like

DB::table('posts as p1')->leftJoin('posts as p2', function($join){
    $join->on('p1.id', '=', 'p2.id')
})->whereIn(p1.user_id, $user_ids)->groupBy('p1.id')->having(COUNT(*) < 10)->orderBy([id, created_at]);

will work for you. for the reference question

The query was

SELECT user_comments.* FROM user_comments
LEFT OUTER JOIN user_comments user_comments_2
ON user_comments.post_id = user_comments_2.post_id 
    AND user_comments.id < user_comments_2.id    
where user_comments.post_id in (x,x,x) 
GROUP BY user_comments.id 
HAVING COUNT(*) < 3 
ORDER BY user_id, created_at

Roughly in DB query builder, it will be like

DB::table('user_comments as uc1')->leftJoin('user_comments as uc2', function($join){
    $join->on('uc1.post_id', '=', 'uc2.post_id')->andOn(uc1.id < uc2.id);
})->whereIn(uc1.post_id, [x,x,x])->groupBy('uc1.id')->having(COUNT(*) < 3)->orderBy([user_id, created_at]);

I hope this is helpful for you and give you good idea to get it.

answered May 08 '19 at 17:08

Prafulla Kumar Sahu

9,321
11
68
105

Thanks buddy, let me try it. Will update u on whether or not i win – Flash May 08 '19 at 19:35
havent been working ystdy but today i am, Thanks for checking up sir, let me make it work boss – Flash May 09 '19 at 07:51
@Flash in the example query there is `andOn` part , that you may need to add according to your table. – Prafulla Kumar Sahu May 09 '19 at 07:53
`return DB::table('posts as p1') ->leftJoin('posts as p2', function ($join) { $join->on('p1.id', '=', 'p2.id'); }) ->whereIn('p1.user_id', $channelIDs) ->groupBy('p1.id') ->having(COUNT('*') < 10) ->orderBy(['id', 'created_at']);` And am getting this for error, what could i have done wrong: `{ "message": "count(): Parameter must be an array or an object that implements Countable", "status_code": 500 }` – Flash May 09 '19 at 08:55
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/193083/discussion-between-flash-and-prafulla-kumar-sahu). – Flash May 09 '19 at 08:56
@Flash check if ‘@chennelIds’ is array or not. – Prafulla Kumar Sahu May 09 '19 at 09:03
I am on mobile will be on system in 30 minutes. – Prafulla Kumar Sahu May 09 '19 at 09:04
Yeah its an array `$channelIDS` is an array ` ->having(COUNT('*') < 10)` this line seems to be the one with an issue – Flash May 09 '19 at 09:13
@Flash so for `*` you are getting nothing/null, change it to `p1.*` and test. – Prafulla Kumar Sahu May 09 '19 at 09:19

Limiting laravel query using whereIn

2 Answers2