Get Last Items in Many to Many Association

Hello all! recently I came across the problem of returning the last created items in a many to many association, I had this Models:

class Resource < ActiveRecord::Base
    has_many :prospections
end
class Location < ActiveRecord::Base
    has_many :prospections
end
class Prospection < ActiveRecord::Base
    belongs_to :location
    belongs_to :resource
    attr_accessible :amount, :prospection_date
end

So each Resource has many Prospections for a given Location, each with different amounts and dates, there could be also many Resources in a single Location. What I needed to get was the current amount of each Resource at a Location based on the last prospection for that Resource in that Location.

After trying various methods (has many through with uniq, a where clause) to do this without much success I figured out I could do that by grouping the Prospections by the resource ID, and make it have the max ProspectionDate (You could use the createdat field if you have a similar scenario, where you don't have a specific date field)

This is the association I'm using for the Location model:

has_many :current_prospections, :class_name => "Prospection", :group => "resource_id", :having => "MAX(prospection_date)"

similarly this is what I've added in the Resource model:

has_many :current_prospections, :class_name => "Prospection", :group => "location_id", :having => "MAX(prospection_date)"

Now I can iterate those collections and I will only see the last prospection for given resources. It took me a lot of googling to came up with this solution so I decided to write up a Tip.

To sum up, when you need to get the last elements created of a has_many association that has duplicated IDs you should group by the duplicated ID and add a having clause to filter only the element with MAX date.

Resources:

has_many Association Reference