（Laravel）如何按最新的透视值过滤表格

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