How to write this sql query into laravel 5 fluent query builder?

Question

I am working on simple postage tracking system, what i am trying to do with this query is return package with latest tracking status name.

i used DB::select to execute this query it worked perfectly but I couldn't use paginate with it.

DB::select(DB::raw('SELECT p.*, 
       (SELECT status.name 
        FROM   status 
               INNER JOIN tracking 
                       ON tracking.status_id = status.id 
        WHERE  tracking.package_id = p.id 
        ORDER  BY tracking.id DESC 
        LIMIT  1) AS status_name 
FROM   packages AS p 
WHERE  p.is_deleted = 0 
ORDER  BY p.id DESC'));

so is there any way i could use paginate with it..? OR can i use DB::table instead of DB::select

here is my tables

packages table:
+----+-------------+------------+
| id | tracking_no | account_no |
+----+-------------+------------+
|  3 |      852369 |      90905 |
+----+-------------+------------+

tracking table:
+----+------------+-----------+
| id | package_id | status_id |
+----+------------+-----------+
|  3 |          3 |         1 |
|  9 |          3 |         2 |
| 10 |          3 |         3 |
+----+------------+-----------+

status table:
+----+------------------+
| id |       name       |
+----+------------------+
|  1 | Ready            |
|  2 | Out for delivery |
|  3 | Delivered        |
+----+------------------+

here is my query

SELECT p.*, 
       (SELECT status.name 
        FROM   status 
               INNER JOIN tracking 
                       ON tracking.status_id = status.id 
        WHERE  tracking.package_id = p.id 
        ORDER  BY tracking.id DESC 
        LIMIT  1) AS status_name 
FROM   packages AS p 
WHERE  p.is_deleted = 0 
ORDER  BY p.id DESC

May be this helps : http://stackoverflow.com/questions/24823915/how-to-select-from-subquery-using-laravel-query-builder — Ajay Makwana, Feb 20 '16 at 09:44

oseintow · Accepted Answer · 2016-02-20T14:49:00.010

DB::select returns an array not a collection/object so pagination can not be directly called on it. Use DB::table or eloquent if u need pagination on your query. You can try this.

 DB::table('packages')
      ->select(['packages.*',DB::raw('(SELECT status.name 
           FROM   status 
           INNER JOIN tracking 
           ON tracking.status_id = status.id 
           WHERE  tracking.package_id = packages.id 
           ORDER  BY tracking.id DESC 
           LIMIT  1) as status_name')])
      ->where('packages.is_deleted','=',0)
      ->orderBy('packages.id','desc')->paginate();

Hope it helps

How to write this sql query into laravel 5 fluent query builder?

1 Answers1