How to sort child table on fields in the associated parent

Rails
1

For an Xray file system patients have folders and folders have studies.
That is:
patients folders studies
id id id
name patient_id folder_id
… label type
… …

The list of studies with their associated folder and patient might look
like this:
name folder_label study_type
Brown,Sam brsa1 CXR
Jones,Gary jg99999 CXR
Jones,Gary joga15 CT
Smith,Rob smro4 CXR
Smith,Rob smro4 CT

I want to present my user with a list of all studies of type ‘CXR’
sorted by the patient name and folder label.

Rails seems to want us to do this:

In the models:
Link patients, folders and studies thru has_many and belongs_to
relationships.

In the controllers provide arrays of records with:
@studies= Study.find(:all, :condition => “type = ‘CXR’” )

In the list view do:
<% for study in @studies -%>

<%= study.folder.patient.name %> <%= study.folder.label %> <%= study.type %> <% end -%>

This seems to require a new query to the db for each row AND I don’t see
any reasonable way to sort the list by name and label. Sorting would
have to get into the pagination code. Ugly!

An SQL query to do this in one step would look like:
SELECT p.name, f.label, s.type
FROM patients p, folders f, studies s
WHERE f.id = s.folder_id
AND p.id = f.patient_id
AND s.type = ‘CXR’
ORDER BY p.name, f.label

Is there a way to put this into a model so that the controller and view
code might simply deal with:
@explicated_studies = ExplicatedStudy.find(:all, :condition => “type in
(‘CXR’, ‘CT’)” )
and:
…

<%= explicated_study.name %> <%= explicated_study.label %> ..

acts_as_list appears to allow accessing the linked tables thru the
patients table. However, if I want a list of all studies that cuts
across all folders and patients and want to sort the list by fields in
folders and patients, acts_as_list doesn’t seem to provide any help.

2

This may help, but it’s only partial pointers to what might be a
solution.

Seems like you should be able to do what you want in the model, as
you suggest. Two things that might help get there are :order
and :joins. :order => does what it sounds like: orders
results on that field. :joins creates a join of one or more tables.
It could be used something like Study.find(:all, :conditions "type =
‘CXR’, :joins “as st inner join patients as pa on st.patient_id =
pa.id” – though you may be able to get what you need just by the
automatic links in Rails. The snag with joins is that the stuff it
returns seems to be read only, and thus would require massaging for
eg, updates. The syntax to do more tables would be from your database.

hope this helps.

…
…
John B.
t: +44 20 7700 1230 f: +44 20 7700 5255