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.

user_5413124 · Answer

I am also stuck with this type of problem but I got a solution and its working fine for me!

use DB::unprepared() for this type of query :

$path = base_path() . "/storage/agency.sql";
$sql = file_get_contents($path);
DB::unprepared(DB::raw($sql));

All SQL commands in Laravel are prepared by default, but sometimes you need to execute a command in an unprepared mode, because some commands in some database cannot be ran in prepared mode.

user_3720905 · Answer

Is this what you meant?

$result = DB::Select("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");

user_2959806 · Answer

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.

user_3024155 · Answer

You can use the following package for this purpose.

Features

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

SQL to Laravel Query Builder, A Converter written in PHP

raw SQL to laravel query

6 Answers