时间戳

首页 > 解决方案 > Laravel 自连接查询

php - Laravel 自连接查询

问题描述

我正在尝试在 Laravel 中执行自联接查询。我正在尝试执行与原始查询相同的操作

  $data['d_meetings'] =DB::select("select t1.*,t2.* from `leads_followup_details` as `t1` inner join `leads_followup_details` as `t2` on `t1`.`leads_enquiry_details_enquiry_id` = `t2`.`leads_enquiry_details_enquiry_id` inner join `leads_enquiry_details` on `enquiry_id` = `t1`.`leads_enquiry_details_enquiry_id` where  `t1`.`followup_meeting` = 'direct' and t1.followup_id<t2.followup_id and t1.followup_next_followup_date=t2.followup_date and `t2`.`followup_current_meeting` = 'direct'  and `enquiry_deleted` = 1 group by `t1`.`followup_id`");



$data['d_meetings'] = DB::table('leads_followup_details as t1')
        ->join('leads_followup_details as t2', 't1.leads_enquiry_details_enquiry_id', 't2.leads_enquiry_details_enquiry_id')
        ->where('t2.followup_id', '>', 't1.followup_id')
        ->where('t2.followup_id', '!=', 't1.followup_id')
        ->whereDate('t1.followup_next_followup_date', '=', 't2.followup_date')
        ->where('t1.followup_meeting', 'direct')
        ->join('leads_enquiry_details', 'enquiry_id', 't1.leads_enquiry_details_enquiry_id')->where('enquiry_deleted', 1)
        ->groupBy('t1.followup_id')->get();

but this is not working as my expectation .

请帮忙

标签： phpmysqllaraveljoinself-join

解决方案

看来->whereDate('t2.followup_date','=','t2.followup_next_followup_date') 您的 SQL 不匹配and t1.followup_next_followup_date=t2.followup_date

如果您以与 SQL 相同的顺序写出 Laravel 查询（并观察重新添加的->where('t2.followup_current_meeting', 'direct')），可能会更容易：

  $data['d_meetings'] = DB::table('leads_followup_details as t1')
                           ->join('leads_followup_details as t2',
                                  't1.leads_enquiry_details_enquiry_id',
                                  't2.leads_enquiry_details_enquiry_id')
                           ->join('leads_enquiry_details','enquiry_id',
                                  't1.leads_enquiry_details_enquiry_id')
                           ->where('t1.followup_id','<>','t2.followup_id')
                           ->where('t1.followup_id','<','t2.followup_id')
                           ->where('t1.followup_meeting', 'direct')
                           ->where('t2.followup_current_meeting', 'direct')                           
                           ->whereDate('t1.followup_next_followup_date','=',
                                       't2.followup_date')
                           ->where('enquiry_deleted',1)
                           ->groupBy('t1.followup_id')->get();

推荐阅读