Combining different select sum queries with different where from same table (Laravel way)

Question

I have slightly different select queries to read data from same table.

select user_id
     , SUM(credit_movement) as testing_fees
  from transactions
 where `type` in ('Testing', 'Testing Data') 
   and `user_id` in (118,124,352 ...)
group by `user_id`

select user_id, SUM(credit_movement) as production_fees 
from `transactions` 
where `type` in ('Production', 'Production Data') and `user_id` in (152,521,1341, ...)
group by `user_id`

The type and user_id are changing.

In Laravel, I ended up using 1 query for each type but there are 10 types which means 10 db connections - which is not good.

$values['testing_fees'] = \DB::table("transactions")
     ->select(\DB::raw("user_id, SUM(credit_movement) as testing_fees"))
     ->whereIn('type', ["Testing", "Testing Data"])
     ->whereIn('user_id', $userIdsToBeUsed)
     ->whereDate('created_at', '>=', $fromDate->toDateString())
     ->whereDate('created_at', '<=', $toDate->toDateString())
     ->groupBy('user_id')
     ->get();

$values['production_fees'] = \DB::table("transactions")
     ->select(\DB::raw("user_id, SUM(credit_movement) as production_fees"))
     ->whereIn('type', ["Production", "Production Data"])
     ->whereIn('user_id', $userIdsToBeUsed)
     ->whereDate('created_at', '>=', $fromDate->toDateString()) // this is common
     ->whereDate('created_at', '<=', $toDate->toDateString())   // this is common
     ->groupBy('user_id')                                       // this is common
     ->get();

What is a good way of combining these queries into one query so that I make 1 database connection to get all data?

(I'm looking for Laravel Query Builder way of achieving this)

Answer 1

If you want to avoid confusion as to which is which, you can have a 3rd key which tells you what kind of data you are dealing with. Looking at Laravel docs, your code should look like:

<?php

$values['testing_fees'] = \DB::table("transactions")
     ->select(\DB::raw("user_id, SUM(credit_movement) as testing_fees,'testing as type'"))
     ->whereIn('type', ["Testing", "Testing Data"])
     ->whereIn('user_id', $userIdsToBeUsed)
     ->whereDate('created_at', '>=', $fromDate->toDateString())
     ->whereDate('created_at', '<=', $toDate->toDateString())
     ->groupBy('user_id');


$values['production_fees'] = \DB::table("transactions")
     ->select(\DB::raw("user_id, SUM(credit_movement) as production_fees,'production' as type"))
     ->whereIn('type', ["Production", "Production Data"])
     ->whereIn('user_id', $userIdsToBeUsed)
     ->whereDate('created_at', '>=', $fromDate->toDateString()) // this is common
     ->whereDate('created_at', '<=', $toDate->toDateString())   // this is common
     ->groupBy('user_id');                                      // this is common


// and so on for $values

$query = array_shift($values);


foreach($values as $key => $sub_query){
    $query->union($sub_query);
}


$data = $query->get();

dd($data);

Note: The ->get() only applies at the end after we have unioned all the subqueries.