How do I find the last record in a relationship?

I have an item that has a history of different states.

I would like to find all items where the most recent state is “public”.

This is something that can be done in SQL. Something similar to below should work on most databases (but I’m a bit rusty on raw SQL) …

However, this is nasty.

One, we’re including SQL in our app (which is OK but should be something you think hard about before you do it). Secondly, it’s using a sub-select, which can have performance issues (although depending upon your database there may be a more efficient way of doing this). Thirdly, we’re passing a parameter into our query – in this case it’s a constant, but if it were user-supplied we would need to escape it to prevent SQL injection attacks.

But there is a much easier way of doing this.

If we treat the states table as a historical audit trail, and an audit trail alone, we can just stick a new field on our items table. Let’s call it “current_state”.

Then all public items, we can define a scope and just call:

This is exposes no SQL, is database-independent and efficient and is automatically escaped. If we wanted parameters we could use:

Finally, we just need to ensure that every time the state changes, the new audit record is added.

The traditional Rails way to do this would be with a callback:

This is quite neat – we have a before_update callback defined that looks if the current_state has changed (a bit of Rails magic there) and if so, it records the history record.

However, it is hidden away at the bottom of your model and happens “magically”. Which is great, but always makes me nervous on large projects – there you want everything to be as explicit as possible.

So I would do this by defining an UpdatesItem command object that always performs both actions:

The reason I like this is that it’s easy to understand – the process is explicitly laid out in front of, one line at a time.

So, to recap: you want to avoid adding complex database code into your app whenever possible. Rails makes it really easy to store your current state on your main model so you can give each table a single purpose – one for the item’s current state, one for the history.