samedi 5 septembre 2020

Combine two group by queries with query builder

I have two similar groupBy queries this:

DB::table('orders')
    ->join('users', 'orders.user_id', '=', 'users.id')
    ->groupBy('users.sponsor_id')
    ->selectRaw('users.sponsor_id, sum(points) as total_points_a')                
    ->get();

and this one:

DB::table('orders')
    ->join('users', 'orders.user_id', '=', 'users.id')
    ->join('users as leader', 'users.sponsor_id' = 'leader.id')
    ->groupBy('leader.sponsor_id')
    ->selectRaw('leader.sponsor_id, sum(points) as total_points_b')                
    ->get();

I don't see a way how to combine them smartly. However, I need as end result the total_points_a and total_points_b for each sponsor_id. Not every sponsor_id is in the result of both queries.

I managed to merge both results with Laravel collections, but this is quite slow. Is it possible to somehow merge the queries, so that one gets sponsor_id, total_points_a, total_points_b as the result of one query?



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire