Combine two group by queries with query builder

Question

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?

Maybe a good candidate for subquery join. Laravel has `joinSub` https://laravel.com/docs/7.x/queries#joins (check under subquery joins) — user3532758, Sep 05 '20 at 15:24
Have you tried to use the methods of the left/right/cross join? — ktscript, Sep 05 '20 at 15:32

score 0 · Answer 1 · answered Sep 05 '20 at 18:41

As user3532758 suggested, subquery joins is what I needed.

In order to really show all all results, I had modify the first query to fetch all users using a left join. From this, I could create a left join sub.

$secondQuery = 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');

$result = DB::table('users')
    ->leftjoin('orders', 'orders.user_id', '=', 'users.id')
    ->groupBy('users.sponsor_id')
    ->selectRaw('users.sponsor_id, sum(points) as total_points_a, bonus_b.total_points_b')    
    ->leftJoinSub($secondQuery, 'bonus_b', function ($join) {
               $join->on('users.sponsor_id', '=', 'bonus_b.sponsor_id');
     })            
    ->get();

Combine two group by queries with query builder

1 Answers1