Rails - Aug 2006 - How to sort child table on fields in the associated parent

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 -%>
 <tr>
  <td><%= study.folder.patient.name %></td>
  <td><%= study.folder.label %></td>
  <td><%= study.type %></td>
 </tr>
 <% 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:
 ..
  <td><%= explicated_study.name %></td>
  <td><%= explicated_study.label %></td>
 ..

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.

-- 
Posted via http://www.ruby-forum.com/.

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 => <field> 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 Browning
t: +44 20 7700 1230     f: +44 20 7700 5255


On 17 Aug 2006, at 17:10, DocPneumo wrote:
> 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 -%>
>  <tr>
>   <td><%= study.folder.patient.name %></td>
>   <td><%= study.folder.label %></td>
>   <td><%= study.type %></td>
>  </tr>
>  <% 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:
>  ..
>   <td><%= explicated_study.name %></td>
>   <td><%= explicated_study.label %></td>
>  ..
>
> 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.
>
> -- 
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060817/f2789429/attachment-0001.html