How can I order by attributes of multiple subclasses that inherit from the same base class in Ruby on Rails?

Question

I have 4 classes - Patient, Doctor, Person, and Appointment. Patient and Doctor are subclasses of Person. Appointment belongs to Patient and belongs to Doctor.

I want to write an AR statement to sort appointments by the patient's last name and another to sort by the doctor's last name, as all appointments will be in a sortable table. I am a bit confused as to what to put in the "order" option of the AR find statement. If I put

:order => 'patient.last_name'

I get a mysql error - "Unknown column 'patient.last_name' Which makes sense because there is no patient column, it is a patient_id referring to a foreign "person" object. Of course I can sort by person.last_name but I am not sure how to specify which type of person to sort by - doctor or patient.

I should also note that I am using the include option to eager load the patient and doctor.

UPDATE

There is only a person table and a appointments table. The patient and doctor inherit from the person. patients.last_name will not work because there is no patients table.

The AR statement is:

find  :all,
                :include => [:doctor, :patient], 
                :order => 'something needs to go here'   

The 'something needs to go here' should be a statement to order by either the doctor's last name or the patient's last name.

Answer 1

You can do this:

Appointment.find(:all, :include => {:patient}, :order => 'people.last_name')

What you're doing is grabbing all the appointments, and their associated patients at the same time. You don't have to worry about patients vs doctors because all the people rows retrieved will be patient records.

In order to have a doctor-centric list, just change :patient to :doctor in the above example.

EDIT: I figured out the solution when you eager load both patients and doctors. It gets a little complex. First, I recreated a simple version of your 4 models in a blank rails app, then tried to run the find with :order => 'patients.name':

Appointment.find(:all, :include => [:patient, :doctor], :order => 'patients.name')

Of course if failed, but it also spit out the SQL query it attempted:

SELECT
  "appointments"."id" AS t0_r0, 
  "appointments"."name" AS t0_r1, 
  "appointments"."doctor_id" AS t0_r2, 
  "appointments"."patient_id" AS t0_r3, 
  "appointments"."created_at" AS t0_r4, 
  "appointments"."updated_at" AS t0_r5, 

  "people"."id" AS t1_r0, 
  "people"."name" AS t1_r1, 
  "people"."type" AS t1_r2, 
  "people"."created_at" AS t1_r3, 
  "people"."updated_at" AS t1_r4, 

  "doctors_appointments"."id" AS t2_r0, 
  "doctors_appointments"."name" AS t2_r1,
  "doctors_appointments"."type" AS t2_r2, 
  "doctors_appointments"."created_at" AS t2_r3, 
  "doctors_appointments"."updated_at" AS t2_r4
FROM "appointments"
LEFT OUTER JOIN "people" ON "people".id = "appointments".patient_id AND  ("people"."type" = 'Patient' )  
LEFT OUTER JOIN "people" doctors_appointments ON "doctors_appointments".id = "appointments".doctor_id AND  ("doctors_appointments"."type" = 'Doctor' )  
ORDER BY patients.name

Now we can see how rails forms a query like this. The first association to use a given table gets the table name directly - "people". Subsequent associations get a combo of the association and original table - "doctors_appointments".

It may seem a little messy, but this call gives you ordered by patients:

Appointment.find(:all, :include => [:patient, :doctor], :order => 'people.name')

And this one gives you ordered by doctors:

Appointment.find(:all, :include => [:patient, :doctor], :order => 'doctors_appointments.name')

Of course, in my example I just had a simple name field for each person, and you'll be using "last_name" instead. But you get the idea. Does this work for you?

ONE LAST EDIT:

I would put these in finders, so you don't need to mess with the table names anywhere else in your code. I'd do it like this:

class << self
  def order_by_patient(field='last_name')
    find(:all, :include => [:patient, :doctor], :order => "people.#{field}")
  end

  def order_by_doctor(field='last_name')
    find(:all, :include => [:patient, :doctor], :order => "doctors_appointments.#{field}")
  end
end

Now you can call them from anywhere, and even sort by the field you want.

Answer 2

I think you might need to use a manual join rather than an include.

See the Active Record Querying Guide on Joining Tables.

You can then create an alias for the table names, and order on these accordingly.

find.all(:joins => 'LEFT OUTER JOIN people dr ON dr.id = appointments.id'
  :order => dr.last_name
)

Or something similar for your database.

Alternatively, you can add a "sort" column that holds an integer. All doctors have a 1, patients have a 0. You can then ORDER BY last_name, sort_column, and the results will be arranged accordingly within the last_name group based on the doctor/patient sort value.

Note: I haven't had my coffee yet this morning so that join is possibly all out of wack, but you get the general idea I hope.