Rails: With models A has_many B belongs_to C return A where B belongs to C and B is most recently created for given A?

Question

I have three models A, B, and C, where A has_many B; B belongs to C, and B has a created_on timestamp. C has_many A through B.

I want to return all A where A has a B that belongs to C and where B is the most recently created B for that A.

I can do this with a method loop. Can this be done solely with named_scopes?, or is some other elegant/efficient manner?

As per request for real world (TM) examples A, B and C can be imagined as, for instance, Pets (A), PetsName (B), and Names (C). PetsNames are given to Pets at a specific time, and any given Name can be given to many Pets. Any Pet can have many Names (a :through relationship). For a given Name I want all of the Pets in which that Name is the most recently created PetName for that Pet. The call may look something like @name.pets.most_recently_named_as

Answer 1

The Rails way to do this is a named scope on pets.

Something like this:

class Pets
  has_many :pet_names
  has_many :names, :through => :pet_names

  named_scope :with_current_name, lambda {|name| 
    { :joins => "JOIN (pet_names pna, names) " +
        "ON (pna.pet_id = pets.id AND pna.name_id = names.id) " +
        "LEFT JOIN pet_names pnb " +
        "ON (pnb.pet_id = pets.id AND pna.created_at < pnb.created_at)", 
      :conditions => ["pnb.id IS NULL AND names.id = ? ", name]
    }
  }
end

Pets.with_current_name(@name)
@name.pets.with_current_name(@name)

To keep things name centric you could always define a method on C/Name that invokes the named scope.

You could always do

class Name < ActiveRecord::Base
  has_many :pet_names
  has_many :pets, :through => :pet_names

  def currently_named_pets
    Pets.with_current_name(self)
  end
end

And

@name.currently_named_pets

It's a pretty complicated join. Which is an indicator that you should probably should rethink the way you store this information. Why is it so important that Names be in a separate table?

You might be better off doing something like this:

After adding name and old_name columns to Pet:

class Pet < ActiveRecord::Base
  serialize :old_name, Array
  def after_initialization 
    @old_name ||= []
  end

  def name= new_name
    self.old_name << new_name
    self.name = new_name
  end

  named_scope :with_name, lambda {|name|
     { :conditions => {:name => name}}
  }
end