Can anybody please help me to write a DB query version of the SQL statement below. I need a little help around the select statement and the partitioned joins.
I have managed to do this so far.
$query = DB::table(raw('SapakInAdminOrder a'))
->select(raw('a.*'))
->leftJoin(raw('currency cu'), 'a.currency', '=', 'cu.id')
->leftJoin(raw('moodboards m'), 'a.orderMoodboardID', '=', 'm.id')
->join(raw('clients b'), 'a.clientID', '=', 'b.id')
->leftJoin(raw('moodboards mc'), 'b.moodboardID', 'mc.id')
->join(raw('sapakim c'), 'b.sapakID', '=', 'c.id')
->leftJoin(raw('sapakim sm'), 'c.managerid', '=', 'sm.id')
->leftJoin(raw('products p'), 'a.productKey', '=', 'p.id')
->where(function ($query) {
$query->whereNull('a.isDeleted');
$query->orWhere('a.isDeleted', '!=', 1);
});
But I need to achieve this.
select * from (select ROW_NUMBER() OVER(ORDER BY case when (indesign.status=4 or indesign.statusdate is null) then getdate()+2 else indesign.statusdate end ASC) AS RowNum,a.*
FROM sapakInAdminOrder a
left join currency cu on cu.id=a.currency
left join moodboards m on m.id=a.orderMoodboardID
inner join Clients b on a.clientID=b.id
left join moodboards mc on mc.id=b.moodboardID
inner join Sapakim c on b.sapakID=c.id
left join Sapakim sm on sm.id=c.managerid
left join products p on p.id=a.productKey
left join (select * from (select ROW_NUMBER() over(PARTITION BY orderID ORDER BY id DESC) r, * from orderCommunication ) f where r=1) chat on chat.orderId = a.id
left join (select id,[status],orderid,approveSMSDate,coverImage,statusDate from (SELECT id,[status],statusDate,approveSMSDate,coverImage,orderid,ROW_NUMBER() OVER(PARTITION BY orderid ORDER BY id DESC) AS r FROM SapakimInAdminDesigns) f where r=1) indesign on a.id=indesign.orderid
where (a.isDeleted is null or a.isDeleted != 1) and
c.inAdminManagerID=(select id from sapakim where sapakguid='test') and
c.sapakguid='test' and
a.isFreeDesign=0 and
a.transactionID = -1 and
(a.designerPaid is null or a.designerPaid=0) and
(chat.sentToPrinter is null and chat.sentToManager is null and chat.sentToDesigner is null)
) bb where RowNum>=1 and RowNum<31
ORDER BY RowNum asc
I can do the simple ones but couldn't quite really wrap my head around the partitioned joins and the select statement.
I would really appreciate a help on this.
Thanks in advance.
from Newest questions tagged laravel-5 - Stack Overflow http://ift.tt/2mqg5Ud
via IFTTT
Aucun commentaire:
Enregistrer un commentaire