将不同的select sum查询与同一表中不同的where组合（Laravel方式）

Question

我有稍微不同的选择查询从同一个表中读取数据。

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`

type和user_id正在更改。

在Laravel中，我最终为每种类型使用了1个查询，但是有10种类型，这意味着10 db连接——这不好。

$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();

将这些查询合并到一个查询中，以便建立一个数据库连接以获取所有数据的好方法是什么？

（我正在寻找Laravel查询生成器实现这一点的方法）

Answer 1

如果您想避免关于哪个是哪个的混淆，您可以使用第三个键来告诉您正在处理的数据类型。查看Laravel文档，您的代码应该如下所示：

<?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);

注：-

Answer 2

您可以使用UNION关键字：https://www.w3schools.com/sql/sql_ref_union.asp

因此，组合查询将如下所示：

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`
UNION
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`

如果需要允许复制，请使用UNION ALL而不是UNION