title: (Laravel) How to filter table by newest pivot value
body: <p>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).</p>
  
  <p>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.</p>
  
  <p>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?</p>
  
  <p>(EDIT, additional clarification: The CURRENT status of an order is the entry in the status log with the newest 'created_at' date.)</p>
  
  <p>Here is some examples of the schemas:</p>
  
  <pre><code>mysql&gt; 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&gt; 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&gt; 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 |
  +----+----------------+----------------+---------------------+---------------------+
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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")
  </code></pre>