"Section" has_many versioned "Articles" -- How can I get the freshest subset?

Question

Hi all!

I have a Model called Section which has many articles (Article). These articles are versioned (a column named version stores their version no.) and I want the freshest to be retrieved.

The SQL query which would retrieve all articles from section_id 2 is:

SELECT * FROM `articles`
WHERE `section_id`=2
AND `version` IN
(
   SELECT MAX(version) FROM `articles`
   WHERE `section_id`=2
)

I've been trying to make, with no luck, a named scope at the Article Model class which look this way:

named_scope :last_version,
            :conditions => ["version IN 
                            (SELECT MAX(version) FROM ?
                             WHERE section_id = ?)", table_name, section.id]

A named scope for fetching whichever version I need is working greatly as follows:

named_scope :version, lambda {|v| { :conditions => ["version = ?", v] }}

I wouldn't like to end using find_by_sql as I'm trying to keep all as high-level as I can. Any suggestion or insight will be welcome. Thanks in advance!

Answer 1

I would take a look at some plugins for versioning like acts as versioned or version fu or something else.

If you really want to get it working the way you have it now, I would add a boolean column that marks if it is the most current version. It would be easy to run through and add that for each column and get the data current. You could easily keep it up-to-date with saving callbacks.

Then you can add a named scope for latest on the Articles that checks the boolean

named_scope :latest, :conditions => ["latest = ?", true]

So for a section you can do:

Section.find(params[:id]).articles.latest

Update: Since you can't add anything to the db schema, I looked back at your attempt at the named scope.

named_scope :last_version, lambda {|section| { :conditions => ["version IN 
                        (SELECT MAX(version) FROM Articles
                         WHERE section_id = ?)", section.id] } }

You should be able to then do

section = Section.find(id)
section.articles.last_version(section)

It isn't the cleanest with that need to throw the section to the lambda, but I don't think there is another way since you don't have much available to you until later in the object loading, which is why I think your version was failing.