php - Laravel Eloquent GroupBy Many To One Relationship

Question

I have the below table structure :

user table

id	name	balance
1	user 1	4
2	user 2	2
3	user 3	2

Answer 1

To get your desired result set you can perform 2 queries, one will get the special user's ids and second will be using these ids to bifurcate between users

In plain SQL the query would look like

select 
  provider,
  sum(case when user_id not in(1,2) then 1 else 0 end) user_count,
  sum(case when user_id not in(1,2) then amount else 0 end) user_amount,
  sum(case when user_id in(1,2) then 1 else 0 end) special_user_count,
  sum(case when user_id in(1,2) then amount else 0 end) special_user_amount,
  sum(amount) total
from orders
group by provider

DEMO

In laravel you can use query builder with raw method

$specialUserIds = SpecialUsers::where('status' , 1)->pluck('user_id')->toArray();


$orders = DB::table('orders')
            ->select('provider',
                DB::raw('sum(case when user_id not in('.$specialUserIds.') then 1 else 0 end) user_count'),
                DB::raw('sum(case when user_id not in('.$specialUserIds.') then amount else 0 end) user_amount'),
                DB::raw('sum(case when user_id in('.$specialUserIds.') then 1 else 0 end) special_user_count'),
                DB::raw('sum(case when user_id in('.$specialUserIds.') then amount else 0 end) special_user_amount'),
                DB::raw('sum(amount) total')
            )
            ->whereNotNull('provider')
            ->whereBetween('created_at', [$start_1, $end_1])
            ->groupBy('provider')
            ->get();

Or you can use parameter binding using selectRaw

Also if there will be only one row in special user table for a user I would suggest to move that status attribute in your main user table instead of creating 2 tables with a one to one relation