Rails Associations has_one Latest Record

Question

I have the following model:

class Section < ActiveRecord::Base
  belongs_to :page
  has_many :revisions, :class_name => 'SectionRevision', :foreign_key => 'section_id'
  has_many :references

  has_many :revisions, :class_name => 'SectionRevision', 
                       :foreign_key => 'section_id'

  delegate :position, to: :current_revision

  def current_revision
    self.revisions.order('created_at DESC').first
  end
end

Where current_revision is the most recently created revision. Is it possible to turn current_revision into an association so I can perform query like Section.where("current_revision.parent_section_id = '1'")? Or should I add a current_revision column to my database instead of trying to create it virtually or through associations?

Solution 1

I understand you want to get the sections where the last revision of each section has a parent_section_id = 1;

I have a similar situation, first, this is the SQL (please think the categories as sections for you, posts as revisions and user_id as parent_section_id -sorry if I don't move the code to your need but I have to go):

SELECT categories.*, MAX(posts.id) as M
FROM `categories` 
INNER JOIN `posts` 
ON `posts`.`category_id` = `categories`.`id` 
WHERE `posts`.`user_id` = 1
GROUP BY posts.user_id
having M = (select id from posts where category_id=categories.id order by id desc limit 1)

And this is the query in Rails:

Category.select("categories.*, MAX(posts.id) as M").joins(:posts).where(:posts => {:user_id => 1}).group("posts.user_id").having("M = (select id from posts where category_id=categories.id order by id desc limit 1)")

This works, it is ugly, I think the best way is to "cut" the query, but if you have too many sections that would be a problem while looping trough them; you can also place this query into a static method, and also, your first idea, have a revision_id inside of your sections table will help to optimize the query, but will drop normalization (sometimes it is needed), and you will have to be updating this field when a new revision is created for that section (so if you are going to be making a lot of revisions in a huge database it maybe would be a bad idea if you have a slow server...)

UPDATE I'm back hehe, I was making some tests, and check this out:

def last_revision
    revisions.last
end

def self.last_sections_for(parent_section_id)
  ids = Section.includes(:revisions).collect{ |c| c.last_revision.id rescue nil }.delete_if {|x| x == nil}

  Section.select("sections.*, MAX(revisions.id) as M")
         .joins(:revisions)
         .where(:revisions => {:parent_section_id => parent_section_id})
         .group("revisions.parent_section_id")
         .having("M IN (?)", ids)
end

I made this query and worked with my tables (hope I named well the params, it is the same Rails query from before but I change the query in the having for optimization); watch out the group; the includes makes it optimal in large datasets, and sorry I couldn't find a way to make a relation with has_one, but I would go with this, but also reconsider the field that you mention at the beginning.

Solution 2

To get the last on a has_many, you would want to do something similar to @jvnill, except add a scope with an ordering to the association:

has_one :current_revision, -> { order created_at: :desc }, 
  class_name: 'SectionRevision', foreign_key: :section_id

This will ensure you get the most recent revision from the database.

Solution 3

You can change it to an association but normally, ordering for has_one or belongs_to association are always interpreted wrongly when used on queries. In your question, when you turn that into an association, that would be

has_one :current_revision, class_name: 'SectionRevision', foreign_key: :section_id, order: 'created_at DESC'

The problem with this is that when you try to combine this with other queries, it will normally give you the wrong record.

>> record.current_revision
   # gives you the last revision
>> record.joins(:current_revision).where(section_revisions: { id: 1 })
   # searches for the revision where the id is 1 ordered by created_at DESC

So I suggest you to add a current_revision_id instead.

Solution 4

As @jvnill mentions, solutions using order stop working when making bigger queries, because order's scope is the full query and not just the association.

The solution here requires accurate SQL:

  has_one  :current_revision, -> { where("NOT EXISTS (select 1 from section_revisions sr where sr.id > section_revisions.id and sr.section_id = section_revisions.section_id LIMIT 1)") }, class_name: 'SectionRevision', foreign_key: :section_id

View more solutions

Comments

[Augustin Riedinger]

Beautiful that's exactly what I was looking for!

[stevenspiel]

If you have a null constraint on section_revisions.section_id, be sure to not use the has_one helper record.create_current_revision (or record.build_current_revision) or you will get Failed to remove the existing associated current_revision. The record failed to save after its foreign key was set to nil. Instead, always use record.section_revisions.create, which will then be the new current_revision

[Quv]

I'm confused. @jvnill pointed out the order part is dropped when the has_one association is joined, and with your answer it doesn't seem to change.

[jvnill]

my answer is actually the same except it's in the old rails 3.2 format. should've moved the order: 'created_at DESC' to the start so it's easier to see.

[jvnill]

what I want to point out is that when you try to combine it with different queries, you have to be mindful of the caveats. see second code in there for an example.

[Joshua Pinter]

Note, for Rails 4+, you'll need to do has_one :current_revision, lambda{ order("created_at DESC") }, class_name: 'SectionRevision', foreign_key: :section_id instead.

[RaphaMex]

It took me some thinking but I found a way that 100% works. Thanks for sharing your solution and for stating its defects, it saved me a lot of time and bugs!