sql查询到laravel查询与联合

Question

我想将下面的联合查询转换为 laravel 查询

DB::select("select lead_master.lead_stage from lead_master inner join  user_master on 

user_master.user_name=lead_master.assigned_user_name where region in (".$geo_string.") and teams 

in (".$filter_teams_string.") group by (lead_stage) union

select  meeting_hash from meetings_master where assigned_user_name in 

(".$filter_username_string.") and meeting_hash in ('follow_up','first_time') group by 

(meeting_hash) 

 union

 select sales_stage from opportunity_master where sales_stage in ('Identified','QO_to be 

 approved') and assigned_user in (".$filter_username_string.") group by (sales_stage)");

以下查询在 postgre 中正常运行

Answer 1

在开始之前，我假设你了解laravel Eloquent假设您的lead_master 表是LeadMaster Model，也是MeetingsMasterandOpportunityMaster$lead_master = LeadMaster::select("lead_master.lead_stage as stage")    ->join('user_master','user_master.user_name','=','lead_master.assigned_user_name')    ->whereIn('region',$geo) //$geo is an array    ->whereIn('teams',$filter_username) //also an array    ->groupBy('lead_stage');$meetings_master = MeetingsMaster::select('meeting_hash as stage')    ->whereIn('assigned_user_name',$filter_username)    ->whereIn('meeting_hash',['follow_up','first_time'])    ->groupBy('meeting_hash');$opportunity_master = OpportunityMaster::select('sales_stage as stage')    ->whereIn('sales_stage',['Identified','QO_to be approved'])    ->whereIn('assigned_user',filter_username)    ->groupBy('sales_stage');$query = $lead_master->union($meetings_master)->union($opportunity_master)->pluck('stage'); //your resultdd($query);这就是你使用Laravel Eloquent时的样子ps：你不需要为此制作3个变量，我只是用它来方便维护