Sorting list of records by a has_many relationship

Rails
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
herehttp://www.informit.com/articles/article.asp?p=26943&rl=1
);

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

4

Brett W. wrote:

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

Hi Brett, you may want to take a look at acts_as_list for the
appointments model. It gives you a number of handy methods like
.position .first .last