(Laravel) How to filter table by newest pivot value

Question

I have a table of orders (orders), a table of statuses (order_statuses) and a pivot table which is intended to be a status log (order_order_status).

When an order's status is changed, an entry is added to the pivot table. The newest entry in the pivot table for that order will be it's current status.

I need to be able to display all orders which CURRENTLY have a given status. For example, all orders in the 'Quote' status. Is there an eloquent way of building this query?

(EDIT, additional clarification: The CURRENT status of an order is the entry in the status log with the newest 'created_at' date.)

Here is some examples of the schemas:

mysql> SELECT * FROM orders WHERE id = 2;
+----+---------+--------------+---------------+----------------------+---------------------+---------------------+
| id | user_id | order_number | job_reference | accounting_reference | created_at          | updated_at          |
+----+---------+--------------+---------------+----------------------+---------------------+---------------------+
|  2 |      73 | 37-5         | Janis Joplin  | NULL                 | 2018-06-25 02:27:21 | 2018-06-25 02:27:21 |
+----+---------+--------------+---------------+----------------------+---------------------+---------------------+

mysql> SELECT * FROM order_order_status WHERE order_id = 2 ORDER BY created_at;
+------+----------+-----------------+---------+---------------------+---------------------+
| id   | order_id | order_status_id | user_id | created_at          | updated_at          |
+------+----------+-----------------+---------+---------------------+---------------------+
|    2 |        2 |               2 |     753 | 2012-06-27 09:47:00 | 2012-06-27 09:47:00 |
|    3 |        2 |               3 |     753 | 2012-06-27 09:56:00 | 2012-06-27 09:56:00 |
|    4 |        2 |               4 |     753 | 2012-06-27 09:56:00 | 2012-06-27 09:56:00 |
|    5 |        2 |               5 |    1153 | 2012-06-27 10:13:00 | 2012-06-27 10:13:00 |
|    6 |        2 |               6 |    1153 | 2012-06-27 10:13:00 | 2012-06-27 10:13:00 |
|    7 |        2 |              10 |    1153 | 2012-06-27 10:13:00 | 2012-06-27 10:13:00 |
|    8 |        2 |               7 |    1153 | 2012-06-27 10:13:00 | 2012-06-27 10:13:00 |
|    9 |        2 |              10 |    1153 | 2012-06-27 10:42:00 | 2012-06-27 10:42:00 |
|   10 |        2 |               7 |    1153 | 2012-06-27 10:42:00 | 2012-06-27 10:42:00 |
|   11 |        2 |               8 |     753 | 2012-06-27 10:44:00 | 2012-06-27 10:44:00 |
|   12 |        2 |               9 |     753 | 2012-06-27 10:45:00 | 2012-06-27 10:45:00 |
| 2222 |        2 |              10 |      54 | 2013-01-03 12:08:00 | 2013-01-03 12:08:00 |
+------+----------+-----------------+---------+---------------------+---------------------+

mysql> SELECT * FROM order_statuses;
+----+----------------+----------------+---------------------+---------------------+
| id | title          | tag            | created_at          | updated_at          |
+----+----------------+----------------+---------------------+---------------------+
|  1 | Archived Quote | archived_quote | 2018-06-25 02:25:28 | 2018-06-25 02:25:28 |
|  2 | Quote          | quote          | 2018-06-25 02:25:28 | 2018-06-25 02:25:28 |
|  3 | Order          | order          | 2018-06-25 02:25:28 | 2018-06-25 02:25:28 |
|  4 | Confirmed      | confirmed      | 2018-06-25 02:25:28 | 2018-06-25 02:25:28 |
|  5 | Manufacturing  | manufacturing  | 2018-06-25 02:25:28 | 2018-06-25 02:25:28 |
|  6 | Painting       | painting       | 2018-06-25 02:25:28 | 2018-06-25 02:25:28 |
|  7 | Dispatched     | dispatched     | 2018-06-25 02:25:28 | 2018-06-25 02:25:28 |
|  8 | Invoiced       | invoiced       | 2018-06-25 02:25:28 | 2018-06-25 02:25:28 |
|  9 | Paid           | paid           | 2018-06-25 02:25:28 | 2018-06-25 02:25:28 |
| 10 | Closed         | closed         | 2018-06-25 02:25:28 | 2018-06-25 02:25:28 |
| 11 | Archived       | archived       | 2018-06-25 02:25:28 | 2018-06-25 02:25:28 |
+----+----------------+----------------+---------------------+---------------------+

EDIT: Further clarification. THis is the SQL query which returns the required results. I am looking for an eloquent method of getting the same results:

SELECT a.order_status_id, c.* 
FROM order_order_status a
INNER JOIN (
    SELECT order_id, MAX(updated_at) last_date
    FROM order_order_status
    GROUP BY order_id
) b ON a.order_id = b.order_id AND a.updated_at = b.last_date 
INNER JOIN 
        orders c 
    ON c.id = a.order_id 
WHERE a.order_status_id = (SELECT id from order_statuses where tag="closed")

Answer 1

You have to use a Join. Eloquent creates two queries when fetching many to many relations. Can't use order by unfortunately in this case with eloquent.

Update

The query builder is going to be your best bet, unfortunately the accepted answer is going to be extremely inefficient and will not scale well to say the least. Off the top of my head the query would be something like:

$orders = DB::table('orders')
    ->leftJoin('order_order_status', 'orders.id', '=', 'order_order_status.order_id')
    ->leftJoin('order_statuses', function ($join) use ($title) {
         $join->on(
             'order_order_status.order_status_id',
             '=',
             DB::raw('SELECT id FROM order_statuses WHERE title = ' . $title . ' AND order_order_status.order_status_id = order_statuses.id ORDER BY created_at DESC LIMIT 1')
         );
    })
    ->select('orders.*')
    ->groupBy('orders.id')
    ->get();

Answer 2

You need to add some relations in each model in order to join them

orders

namespace App\Model;
use Illuminate\Database\Eloquent\Model;

class orders extends Model 
{
    protected $table = 't_orders';
    protected $primaryKey = 'id';

    // join t_orders with t_order_order_status but get only the latest matching row out of order_order_status
    public function status_log()
    {
        return $this->hasMany('App\Model\order_order_status', 'order_id','id')->orderBy('t_order_order_status.id', 'desc')->limit(1);
    }

}

order_order_status

namespace App\Model;
use Illuminate\Database\Eloquent\Model;

class order_order_status extends Model 
{
    protected $table = 't_order_order_status';
    protected $primaryKey = 'id';

    // join t_orders with t_order_order_status
    public function status_name()
    {
        return $this->hasOne('App\Model\order_statuses', 'id','order_status_id');
    }

}

order_statuses

namespace App\Model;
use Illuminate\Database\Eloquent\Model;

class order_statuses extends Model 
{
    protected $table = 't_order_statuses';
    protected $primaryKey = 'id';

}

Then you would do something like this

App\Model\orders::with('status_log.status_name')->get();

Should get something like this

[  
   {  
      "id":2,
      "user_id":73,
      "order_number":"37-5",
      "job_reference":"Janis Joplin",
      "accounting_reference":null,
      "created_at":"2018-06-25 02:27:21",
      "updated_at":"2018-06-25 02:27:21",
      "status_log":[{  
        "id":4,
        "order_id":2,
        "order_status_id":4,
        "user_id":753,
        "created_at":"2012-06-27 09:56:00",
        "updated_at":"2012-06-27 09:56:00",
        "status_name":{  
           "id":4,
           "title":"Confirmed",
           "tag":"confirmed",
           "created_at":"2018-06-25 02:25:28",
           "updated_at":"2018-06-25 02:25:28"
        }
      }]
   }
]

Note I've prefixed table names with t_ for readability but you can name them same as your models.

UPDATE

For that you can use something like this passing a clusre that will filter based on status id

$status = 3;

$result =  App\Model\orders::with(['status_log'=>function($query) use($status) {

    $query->where('t_order_order_status.order_status_id','=', $status);

    $query->with('status_name');

}])->get();

return $result;

Will give

[{  
      "id":2,
      "user_id":73,
      "order_number":"37-5",
      "job_reference":"Janis Joplin",
      "accounting_reference":null,
      "created_at":"2018-06-25 02:27:21",
      "updated_at":"2018-06-25 02:27:21",
      "status_log":[{                       // will be empty [] for order not in t_order_order_status
        "id":3,
        "order_id":2,
        "order_status_id":3,
        "user_id":753,
        "created_at":"2012-06-27 09:56:00",
        "updated_at":"2012-06-27 09:56:00",
        "status_name":{  
               "id":3,
               "title":"Order",
               "tag":"order",
               "created_at":"2018-06-25 02:25:28",
               "updated_at":"2018-06-25 02:25:28"
        }
     }]

   }]

It will return all orders but you can use status_log to figure out whether it has any record in t_order_order_status table , but in-case you want to remove the "extra" and retain just those orders which have are there in logger table then you might use filter on $result.

return $result->filter(function ($item) {
    return !empty($item->status_log && count($item->status_log));
});

Answer 3

php artisan make:model User

in your User Model :

   protected $table='users';
   public $primaryKey='id';

 public function orders(){
    return $this->belongsToMany('App\Model\Orders','order_order_status','user_id','order_id')->orderby('created_at','desc');
 }

php artisan make:model Order

in your Order Model :

  protected $table='orders';
  public $primaryKey='id';

  public function user(){
    return $this->belongsTo('App\Model\User','user_id');
  }

 public function order_status()
  {
   return $this->belongsToMany('App\Model\OrderStatus','order_order_status','order_id','order_status_id')->withTimestamps();
  }

php artisan make:model OrderStatus

in your OrderStatus Model :

  protected $table='order_statuses';
  public $primaryKey='id';

 public function orders()
    {
        return $this->belongsToMany('App\Model\Order','order_order_status')->with('user')->orderBy('created_at','desc');
    }

php artisan make:controller OrdersController

Now You want to get orders by OrderStatus title='Quote'

in your OrdersController :

use Illuminate\Http\Request;



 public function get_order(Request $request,$title){
         // $title='Quote';
         $orders=OrderStatus::where('title',$title)->with('orders')->get();
         return response()->json($orders);
    }