tomaytotomato
tomaytotomato

Reputation: 4028

MySQL WHERE most recent row column is after a specific date in a joined table?

I am trying to narrow down the resultset of a query, one way I can reduce this is by checking an audit table foo_status, when a particular column barStatus has been set to "Live".

Foo table

+--------+----------+-----------+---------------------+
|   id   |  status  | barStatus |       created       |
+--------+----------+-----------+---------------------+
| 120534 | LIVE     | LIVE      | 13/02/2021 13:21:04 |
+--------+----------+-----------+---------------------+

Essentially the cardinality is (A Foo -- has many --> Foo Status Updates)

The table records when a foo states changes over time, e.g.

+------+-------------------+-------------------+-------+------------------+------------+--------+
|id    |createdOn          |modifiedOn         |version|status            |barStatus   |foo_id  |
+------+-------------------+-------------------+-------+------------------+------------+--------+
|213423|2018-09-02 03:21:56|2018-09-02 03:21:56|0      |ACTIVATED         |UNSURE      |120534  |
|213424|2018-09-02 03:30:22|2018-09-02 03:30:22|0      |REGISTERED        |UNSURE      |120534  |
|213425|2018-09-02 03:30:22|2018-09-02 03:30:22|0      |AWAITING_INTERVIEW|UNSURE      |120534  |
|215396|2018-09-07 10:16:58|2018-09-07 10:16:58|0      |INVITED_TO_JOIN   |UNSURE      |120534  |
|215601|2018-09-07 21:09:13|2018-09-07 21:09:13|0      |INVITED_TO_JOIN   |DBS_RECEIVED|120534  |
|215603|2018-09-07 21:17:12|2018-09-07 21:17:12|0      |READY_FOR_LIVE    |DBS_RECEIVED|120534  |
|218345|2018-09-14 14:42:22|2018-09-14 14:42:22|0      |READY_FOR_LIVE    |LIVE        |120534  |
|218351|2021-02-14 14:45:21|2018-09-14 14:45:21|0      |LIVE              |LIVE        |120534  |
+------+-------------------+-------------------+-------+------------------+------------+--------+

I am wanting to restrict the results set for when a foo has become LIVE for barStatus within 5 days

My current query however returns no results

select count(foo.id)
from foos foo
         left join foo_status_updates fsu on fsu.foo_id = foo.id
where fs.id = 1
  and (foo.currentlyUnavailable IS FALSE)
  and (foo.status in ('READY_FOR_LIVE', 'LIVE'))
  and foo.barStatus = 'LIVE'
  and (SELECT createdOn
       FROM foo_status_updates fsu
       WHERE foo_id = foo.id
         AND barStatus = 'LIVE'
       ORDER BY createdOn
       LIMIT 1) > DATE_SUB(NOW(), INTERVAL 5 DAY)

In summary, I only want to get back Foo records which have had their barStatus set to LIVE recently (within 5 days)

Do I need to select the most recent column inside the JOIN clause?

Upvotes: 1

Views: 41

Answers (1)

James Stevens
James Stevens

Reputation: 431

Looks to me like the AND (SELECT bit should just be

AND foo.id in (
   SELECT foo_id 
   FROM foo_status_updates 
   WHERE createdOn > DATE_SUB(NOW(), INTERVAL 5 DAY)
   AND barStatus = 'Live'
)

Upvotes: 1

Related Questions