Linked Questions

Popular Questions

How to use select in laravel on left outer join as second criteria

Asked by At

I am trying to convert this mysql block to laravel sql but could not succeded in anyway

select tt.id
     , tt.date
     , m1.date
     , m2.date
     , m1.userid
     , m2.userid 
  from tbltickets tt 
  join tblticketreplies m1 
    on tt.id = m1.tid 
  left
  join tblticketreplies m2 
    on m1.tid = m2.tid 
   and m2.date =
     (SELECT min(m3.date) 
       FROM tblticketreplies m3 
      WHERE m3.date > m1.date 
        and m3.tid=m1.tid) 
 where tt.date > NOW() - INTERVAL 1 MONTH;

Laravel Block :

        $rows = DB::table('tbltickets')
        ->join('tblticketreplies as m1', 'tbltickets.id', '=', 'm1.tid')
        ->leftJoin("tblticketreplies as m2", function($join) {
            $join->on('m2.tid', '=', 'm1.tid');
            $join->on('m2.date',"=",DB::raw("SELECT min(m3.date) FROM tblticketreplies m3 WHERE m3.date > m1.date and m3.tid=m1.tid"));
        })
        ->select('tt.id as id,tt.date as first,m1.date as second,m2.date as third ,m1.userid as fid,m2.userid as sid')
        ->get();

does any body know how to use select in outer join or does it possible to do it ?

Related Questions