MySQL - Getting historical "snapshots" from a history table

Question

I have a MySQL database that has verious one to many relationship fields (eg orders and order_items) Each time an order_item is changed, the record is also inserted into the order_item_history table with the timestamp in the modified field.

What I need to do is query the database to get the state of the order based on a particular date. To get the most recent version for a particular order_item, I would

SELECT * from order_item_history where order_item_id=4 and modified <='SEARCH DATE' ORDER by modified DESC limit 1

What I can't figure out is how to write a query that would work like the one above, except return the most recent versions of all items attached to an order based on order_id field

I can do this programmatically in PHP by putting the order_item_ids in an array and running a query for each one, but I have to think that there is a way to do it from MySQL.

Any help would be greatly appreciated.

Answer 1

Try -

SELECT * FROM order_item o
LEFT JOIN order_item_history oh ON o.id=oh.item_id
WHERE o.id=4
ORDER BY o.id DESC
LIMIT 1