Getting more rows per group using groupBy in Eloquent

Question

I'm trying to write an eloquent query which returns up to 3 rows per group (using groupBy). This post is similar but I'm not sure how to apply that solution here.

I have an eloquent model Deal for my MySQL table deal. Each deal can have one of four types, so ultimately there should be at most four groups (or less if there are no deals of a certain type).

I have tried Deal::where('status', 'active')->groupBy('type')->get(); and this returns an array of four objects (one deal for each of the four types), however I am expecting 12 deals, 3 per group (and they do exist in the DB). Similarly, trying Deal::where('status', 'active')->groupBy('type')->take(3)->get(); returns an array of three objects (one group is now missing).

I've accomplished this so far by making four separate DB queries, one for each of the four types but I'd prefer it if this can be done in one. Any thoughts? Thank you!

Answer 1

One of the way you can achieve what you are trying to do is:

Deal:where('status', 'active')
    ->get()
    ->groupBy('type')
    ->map(function($deal) {
        return $deal->take(3);
    });

Of course, you need to check whether the query returns null or empty objects and please note that the query returns all the active Deal(s) (the filtering happens on the Collection) which might be non-efficient if there are lots of Deal(s)