How to laravel eloquent groupby after unionAll?

I want to convert this Mysql code to 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

I changed this code to 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();

the question is how to run groupby after unionAll

That's what I want to changed into eloquent.

total_union group by service_id

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/2vIwc4k
via IFTTT