Raw SQL query on laravel

Question

Can someone help me write the SQL query version for the SQL statement below. I need a little help around the select statement and partitioned joins.

I 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 simple ones, but I could not really wrap my head around the separated connections and the select statement .

I am very grateful for the help in this.

Thanks in advance.

Answer 1

I am also stuck in this type of problem, but I have a solution and its work is excellent 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 run the command in unprepared mode, as some commands in some database cannot run in prepared mode.

Answer 2

Is that 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"); 

Answer 3

Perhaps you should create a database view for these partitioned queries? You can then join the view from the database afterwards.

Technically, these analytic functions are usually not supported by frames.

Answer 4

Check out this new Laravel tool called Orator. It allows you to simply insert legacy SQL and returns the syntax of the Laravel DB Query Builder.

Laravel Orator News Article

Interactive conversion tool

Answer 5

Assuming you want to display this on an interface, use a relationship with an eloquent model, https://laravel.com/docs/master/eloquent-relationships#one-to-one

In the user model there is a one-to-one relation of the callee defined by the phone () method, if you go to watch the show, which IM is supposed to go through the $ user variable do the following

 <?php dd($user->phone); ?> 

I may be missing the whole point of the question if you do not mean it.