raw SQL to laravel query

Question

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.

Answer 1

Maybe you should create a database view for these partitioned queries? Then you can join the view from database afterwards.

Technically these analytical functions are usually not supported by frameworks.

Answer 2

You can use the following package for this purpose.

Features

1. Converts SQL Queries to Laravel Query Builder.
2. Assists on building queries as instructed in Laravel Documentation.
3. Provides options to interact with, for generating different results.

SQL to Laravel Query Builder, A Converter written in PHP