What I'm trying to model is the following: I have a number of Sites
which represent wells. From these sites at certain depths Samples are
collected. Each Sample contains a number of different Subjects
(specimens) found in varying quantities. These are represented by the
SubjectHits model in the SubjectHit.hits field. The Sites also have a
number of geological Formations found at diverse depths. The Formation
top and bottom for a particular Site are saved in
FormationsSite.formtop and FormationsSite.formbot.
On each Subject's view page I need to display a list of where has this
particular species appeared and in what quantity. This list can be
quite large so I need to use the pagination helper. I also need to
display which Sample this hit belongs to and to which Site. So far
I've accomplished this with the following code in the Subject
controller:
$this->set('subjectHits', $this->paginate('SubjectHit',
array('SubjectHit.subject_id'=>$id)));
and on the $paginate property:
var $paginate = array(
'SubjectHit' => array(
'limit' => '50',
'page' => '1',
'contain'=> array('Sample.Site'),
),
);
using Containable to reach Site.name
My problem comes from the following:
I also need to show within which Formation for that particular Site
does that Sample falls within. Basically Sample.depth must be between
FormationsSite.formtop and FormationsSite.formbot.
With a raw query I would do this like this:
select Formation.name, Site.name, Sample.depth, SubjectHit.hits
from subject_hits as SubjectHit
left join samples as Sample on SubjectHit.sample_id = Sample.id
left join sites as Site on Sample.site_id = Site.id
left join formations_sites as FormationsSite on
(Site.id = FormationsSite.site_id
AND Sample.depth <= FormationsSite.formbot
AND Sample.depth >= FormationsSite.formtop)
left join formations as Formation on FormationsSite.formation_id =
Formation.id
where SubjectHit.subject_id = 1234
limit 20
Problem is, I need to paginate these results. If I override paginate()
and paginateCount() for SubjectHit for this specific query it will
interfere with regular pagination for this model when I don't need to
show the Formation. Is there a way I can obtain this information via a
regular find() call? What I'm doing right now is to Contain
Sample.Site.FormationsSite.Formation which brings me every formation
for that particular Site and in the view I walk the array looking for
the one matching the conditions. This, however, is pretty inefficient
and also generates a ton of queries.
Here's some additional info on my schema:
Models:
Site
Sample
SubjectHit
Subject
Formation
FormationsSite
Site hasMany Sample
Sample hasMany SubjectHits
Subject hasMany SubjectHits
Site hasMany FormationsSite
Formation hasMany FormationsSite
Each hasMany relationship has its corresponding belongsTo relationship
as well.
Tables:
sites: id, name
samples: id, site_id, name, depth
subjects: id, name
subject_hits: id, sample_id, subject_id, hits
formations: id, name
formations_sites: id, formation_id, site_id, formtop, formbot
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---
