A MySQL Time Machine
N.B. This post is required for MySQL 5.1 (or earlier) and will likely be needed for newer editions of the RMDBS as well
He did and the laws of known physics haven’t been broken yet, what I am explaining here is representing temporal state of an element (e.g an account) whose historical state can be queried for any given point of time and future state(s) can be set in advance.
In the above illustration the Account is active for the timestamp of the query, Green represents an active account
The tables to be used in this effort will look similar to this
Account actions table
The account table has the usual fields:
- Credit card number
The account_actions table records :
- A unique action ID
- An Action_Type – this is an enumeration of an account action
- An Action_Date – the point where this account action takes come into effect
- Created_Date – the date that this action was created
Oh like this?
8: account.Account_ID = Account_actions.Account_ID
10: Account_actions.Action_Date < NOW();
In the above example we hope to obtain the account name from the Account table and the previous action_type from the Account_Actions table with the account action corresponding to the most recent one, this will produce erroneous results as that JOIN operation does not apply sorting of the results from the joined table to the join even if we were to add an ORDER BY.
In most cases the Action_Type returned would likely be the first one stored into the Account_actions table in the subset of data created from that WHERE clause.
It is entirely possible that this erroneous implementation could lay in wait ready to trip up even experienced SQL Query Craftspeople as it seems to be a correct logical Command
Is there a solution?
Yes in place of joining a pre-existing table we JOIN a table generated by an inline SELECT command which has the necessary sorting and filtering in place.
So to correctly implement this goal we would use the following command
2: Account.Account_ID, Generated_account_actions.Action_Type
8: scheduled_actions.Account_ID, scheduled_actions.Action_Type
12: scheduled_actions.Action_Date < NOW()
13: ORDER BY
14: scheduled_actions.Action_Date DESC
15: ) AS Generated_account_actions
17: account.Account_ID = Generated_account_actions.Account_ID;
This query uses a the results from the filtered and ordered table (under an alias of Generated_account_actions) to perform a JOIN and return the most recent action state for this user account.
I hope this post helps you when making your next killer MySQL backed App, these queries may be performance optimised but I wish to illustrate a point.