Welcome toVigges Developer Community-Open, Learning,Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.4k views
in Technique[技术] by (71.8m points)

php - Laravel Eloquent GroupBy Many To One Relationship

I have the below table structure :

user table

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to Vigges Developer Community for programmer and developer-Open, Learning and Share
...