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 ?