0

I am using Laravel and have a music table, an order_items table and a users table. The music items all have a user and a user can have multiple music items. I am trying to get the top 100 selling music, but I want to return only one music item per user (the top selling one of all their music sales).

My code is:

$items = DB::table('order_items')
->join('music', 'music.id', '=', 'order_items.music_id)
->groupBy('music_id')
->select('order_items.user_id', 'order_items.music_id', DB::raw('count(*) as sold'))
->orderBy(DB::raw('sold_count'), 'desc');

$items = collect($items)->unique('user_id')->values()->take(100);

This does seem to return the data that I need, however the first query returns more than 35K records so takes a while to run. I can place a limit on the initial query however then I can not guarantee that there will be 100 items once they are grouped by user.

Is there a better way to do this? Can it be run in one query?

lam
  • 1
  • 1
  • 1
    You can group by multiple columns https://stackoverflow.com/questions/23185432/group-by-multiple-columns-in-laravel – Charlotte Dunois Aug 01 '16 at 15:45
  • Thanks Charlotte but I have tried groupBy('user_id', 'music_id') and it doesn't seem to have any effect - it still returns multiple music items for a user – lam Aug 01 '16 at 16:37

0 Answers0