The issue is basically that I have 2 models (Sample and
FormationsSite) that share the same parent (Site) and are in turn
related to each other by virtue of 1 field on one (Sample.depth) and 2
fields on the other (FormationsSite.formtop and
FormationsSite.formbot).
I need some way to express that relationship in a way that makes sense
to cake's paginate(). I've also tried to do the filtering with
Containable's conditions option but it appears I can only specify
values on the right side and not field names:
$blah = $this->SubjectHit->find('all', array(
'conditions'=>array(
'SubjectHit.subject_id'=>$id
),
'contain'=>array(
'Sample.Site',
'Sample.Site.FormationsSite.Formation',
'Sample.Site.FormationsSite'=>array(
'conditions'=>array(
'formtop >'=>'SubjectHit.Sample.depth',
),
),
),
));
This generates several queries like this:
SELECT `FormationsSite`.`id`, `FormationsSite`.`formation_id`,
`FormationsSite`.`site_id`, `FormationsSite`.`formtop`,
`FormationsSite`.`formbot` FROM `formations_sites` AS `FormationsSite`
WHERE `formtop` > 'SubjectHit.Sample.depth' AND
`FormationsSite`.`site_id` = (78)
I'd be happy to use a query() if I could paginate it without
interfering with the other pagination needs for the model.
Regards,
Ariel
On Aug 24, 9:49 pm, "David C. Zentgraf" <[EMAIL PROTECTED]> wrote:
> Your question is a bit long, so I'm not quite sure what you're asking.
> It sounds to me like you're having trouble finding() stuff with the
> condition being on an associated model?
> For HABTMs or very deeply nested associations Cake indeed doesn't seem
> to handle it very gracefully yet.
> I recently used this code for a HABTM relationship (Place HABTM
> Categories):
>
> $conditions['Place.id'] = Set::extract(
> '/Place/id',
> $this->Place->Category->find('all', array(
> 'conditions'=> array(' Category.id' => $id),
> 'contain' => 'Place.id'
> ))
> );
>
> It searches for the category with id = $id and gives me an array of
> all Place.ids in that Category.
> Next I'm using this array as a condition when searching for the actual
> places:
>
> $this->paginate = array(
> 'limit' => 10,
> 'conditions' => $conditions,
> 'order' => array('Place.name_e' => 'asc')
> );
> $results = $this->paginate('Place');
>
> $conditions holds a bunch of belongsTo conditions like
> 'Place.location_id' => $location_id etc,
> but also the array of Place.ids I got earlier, which translated to an
> SQL query like
> WHERE `Place`.`id` IN (1, 2, 3, ...), which essentially works like a
> subquery, only in two steps.
>
> Might not be as elegant as a multiple JOIN query, but works well
> enough and purely within Cake.
> Maybe this'll tip you off in the right direction?
>
> On 25 Aug 2008, at 11:18, Ariel Arjona wrote:
>
>
>
> > 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
-~----------~----~----~----~------~----~------~--~---
