Sorting list of records by a has_many relationship...

rubyonrails-talk
1

I've banged my head on this for 2 days, and need to step back and ask for help.

I'm doing what I would think would be a common occurrence - viewing a list of records sorted by the latest item in a has_many relationship.

Contacts have many appointments, with one next_appointment that is the most recent.

My view lists the contacts, using pagination. One of the displayed columns is the next_appointment. I want to support sorting the list on the next_appointment. Problem is I always get *every* appointment for each contact in the list. I can't seem to weed everything except the latest appointment.

Doesn't matter if I use the :include or not, and DISTINCT doesn't work becuase the records are real duplicates. I haven't tried GROUP BY yet because I don't think it's going to weed anything out, though I may be wrong about that.

I have the following relationships:

class Contact < ActiveRecord::Base   has_many :appointment, :dependent => :destroy, :order => 'created_at DESC'   has_one :next_appointment, :class_name => 'Appointment', :order => 'appointment_on DESC' end

class Appointment < ActiveRecord::Base    belongs_to :contact end

Does anybody know a way to handle this situation?

Cheers, Brett

2

I didn’t test this, but the :finder_sql option of the has_many relationship might allow you to get what you want:

has_many :next_appointment,
:class_name => ‘Appointment’, :finder_sql =>‘SELECT * FROM appointments WHERE contact_id = #{id} ORDER BY appointment_on DESC LIMIT 1’

The relationship name is now even less descriptive than it was before since it only really has one appointment, and it doesn’t guarantee that it’s the next appointement (unless you add a ‘appointment_on > now()’ clause to the query).

A more expressive way to go about it might be to add a method to the Contact object called next_appointment that returns the top appointment like:

def next_appointment self.appointments.find :first,:order=>’ appointment_on DESC’, :conditions=>‘appointment_on > now()’ end

Depending on the size of your dataset, you may get better performance by eager loading and using ruby to search the arrays (you can find a great article about that here);

I didn’t test either of these, so they may be slightly off.

3

Jeff,

I gave your suggestions a try. Your has_many works the same as my has_one. Find the contact, and the appointment is the latest one. But, if you :inlcude => appointment, it pulls all appointments for a contact, not just the latest one. And I think I have to eager load (or :join) in order to get the sorting on the appointment field. So I still get a list where there are multiple entries per contact, each with a different appointment date.

The dataset is large enough that I can't pull it all into memory and sort with ruby. That's why I want to find an SQL way so I can use pagination.

I keep thinking either a subselect or group by would work, but I can't seem to get one coded correctly. Any ideas?

Cheers, Brett