php - 如何在 Laravel 中的 unionAll 之后将 group by 添加到 eloquent 查询？

Question

我想将此 Mysql 代码转换为 Laravel Eloquent

select service_id, sum(cnt) from 
(
    select * from 
    (
        select service_id, count(*) cnt from prdouct_notifs 
        join products on products.id = prdouct_notifs.product_id 
        where prdouct_notifs.user_id = 268 
        and prdouct_notifs.deleted_at is null 
        and prdouct_notifs.block = 4 
        group by service_id
    ) first_table 

   union All
   (
       select service_id, count(*) cnt from products
       where products.pro_user_id = 268
       and products.status = 47575 
       group by service_id
  )
) total_union group by service_id

我将此代码更改为 Laravel Eloquent。

$get_product = Product::where('pro_user_id', 268)
    ->where('status', 47575)
    ->groupby('service_id')
    ->selectRaw('service_id ,count(*) cnt');

$get_final_product = prdouct_notifs::join('products', function ($join){
    $join->on('products.id', '=', 'prdouct_notifs.product_id')
        ->where('prdouct_notifs.user_id', 268)
        ->where('prdouct_notifs.block', 4);
})
    ->selectRaw('service_id ,count(*) cnt')
    ->groupby('service_id')
    ->unionAll($get_product)
    ->get();

问题是如何在 unionAll 之后运行 groupby

这就是我想改成雄辩的。

total_union group by service_id

标签： phpmysqllaravellaravel-5eloquent

Answer 1

我的想法是在分组之前将 UNION 放入子查询中。我希望这能回答你的问题。

$product = DB::table('products as p')
    ->where('p.status', 47575)
    ->select('p.service_id as service_id');

$productNotify= DB::table('prdouct_notifs as pn')
    ->select('pn.service_id as service_id')
    ->union($product);

$groupby = DB::query()->fromSub($productNotify, 'p_pn')
    ->select('service_id', DB::raw('COUNT(*) as cnt'))
    ->groupBy('service_id');

产生以下 SQL

select "stud_id", COUNT(*) as total_asset from (
   select * from (select p.service_id as service_id from products as p) 
   union 
   select * from (select pn.service_id as service_id from prdouct_notifs as pn) 
) as p_pn 
group by service_id