Sorting list of records by a has_many relationship...

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

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.

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