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.

It would be helpful to share some SQL to setup a test schema and seed some data alongside your expected outcome — Mike Miller, Nov 22 '17 at 11:59

Aret · Answer 1 · 2017-11-25T14:02:55.260

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.

edited Nov 25 '17 at 14:02

answered Nov 25 '17 at 13:38

Aret

475
3
9

that means there's no way we can do this in laravel? – Wdy Dev Nov 26 '17 at 08:50
Not all DB-s are supporting analytical functions. ORM-s are made to be universal and supporting all the possible DB engines. There is no point to implement these sophisticated queries in ORM, most people even don't know their existence. There are some exeptions, JOOQ for example, but this is not the PHP language (https://blog.jooq.org/2013/11/03/probably-the-coolest-sql-feature-window-functions/). – Aret Nov 27 '17 at 05:31

score 1 · Answer 2 · answered Nov 25 '21 at 08:05

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

score 0 · Answer 3 · answered Nov 15 '17 at 12:57

0

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.

answered Nov 15 '17 at 12:57

Rahul Pawar

1,016
1
8
25

I can simply query it using DB::query() but this is not an elegant solution. – Wdy Dev Nov 15 '17 at 13:12
DB::unprepared runs a raw query, the DB::raw is not required. Loading queries from a text file (especially one in the storage folder) is asking for trouble should the file be compromised. Far better to use the repository pattern and use DB::select() in your source code. – Greg Robson Nov 15 '17 at 22:19

score 0 · Answer 4 · answered Nov 23 '17 at 08:13

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

It would be DB::query() but still thats not what i meant – Wdy Dev Nov 26 '17 at 08:49 — Wdy Dev, Nov 26 '17 at 08:49

score 0 · Answer 5 · answered Dec 12 '17 at 16:49

0

Check out this new Laravel tool called Orator. It lets you simply paste "legacy" SQL and returns Laravel's DB Query Builder syntax.

Laravel Orator News Article

Online Conversion Tool

answered Dec 12 '17 at 16:49

Taylor Cox

111
1
5

score 0 · Answer 6 · answered Dec 28 '17 at 12:40

Assuming you want to display this on the frontend use the relationships wit the eloquent model, https://laravel.com/docs/master/eloquent-relationships#one-to-one

In the model User there is a One to One relation called identified by the method phone(), if you go to the show view which im assuming passes a $user variable do the following

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

I might be missing the whole point of the question if you don't mean this.

raw SQL to laravel query

6 Answers6