Re: Fetching data with join over 3 tables

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Re: Fetching data with join over 3 tables

lloydhome

I have used CakePHP for several months and answered a few posts without
needing to post and I probably don't need to now but I cannot find the
answer.

I have tried setting the recursive var to 2, 3, 4, 5 and no help to my
problem.  I have a User model that has a belongsTo a Subarea that
belongsTo an Area that belongsTo a Region.  The associations are corret
(I think) because the recursion works in the case described above.  My
problem is when a field is in a condition that is not 'close' to the
first model.

regions
------
region_id int primary key,
region_name varchar(50) not null unique key
...snip...

areas
------
area_id int primary key,
area_regionid int references regions(region_id),
area_name varchar ...
...snip...

subareas
------
subarea_id int primary key,
subarea_areaid int references areas(area_id),
subarea_name varchar ...
...snip...

users
------
u_id int primary key,
u_subareaid int references subareas(subarea_id),
login_name varchar(50) not null unique key,
...snip...

class Region extends AppModel {
  var $name = 'Region';
  var $primaryKey = 'region_id';
  var $hasMany = array(
'Area'=>array('className'=>'Area','association'=>'Area','foreignKey'=>'area_regionid')
);
}
class Area extends AppModel {
  var $name = 'Area';
  var $primaryKey = 'area_id';
  var $belongsTo = array('Region'=>
array('className'=>'Region','association'=>'Region',
'foreignKey'=>'area_regionid') );
  var $hasMany = array(
'Subarea'=>array('className'=>'Subarea','association'=>'Subarea','foreignKey'=>'subarea_areaid')
);
}
class Subarea extends AppModel {
  var $name = 'Subarea';
  var $primaryKey = 'subarea_id';
  var $recursive = 2;
  var $belongsTo = array('Area'=>
array('className'=>'Area','association'=>'Area',
'foreignKey'=>'subarea_areaid') );
// ...snip business logic...
}
class User extends AppModel {
  var $name = 'User';
  var $belongsTo = array('Subarea'=>
array('className'=>'Subarea','association'=>'Subarea',
'foreignKey'=>'u_subareaid') );
  var $primaryKey = 'u_id';
// ...snip business logic...
}

if I query from my controller ...
  $result_count =
$this->User->findCount(array('Region.region_name'=>'Canada'), 4);

I get an error in this generated query:
SELECT COUNT(*) AS count FROM `users` AS `User` LEFT JOIN `subareas` AS
`Subarea` ON `User`.`u_subareaid` = `Subarea`.`subarea_id` WHERE
(`Region`.`region_name` = 'Canada')
  1109: Unknown table 'Region' in where clause

If I commit a cardinal sin and modify dbo_source.php and insert the
following 10 lines into the read method before the comment here:
                //DAL: HACK in recursive query joins!
                if ($model->recursive > 1) {
                        foreach($model->__associations as $type) {
                                foreach($model->{$type} as $assoc => $assocData) {
                                        $linkModel =& $model->{$assocData['className']};

                                        $this->__dal__generateRecursiveQuery($model, $linkModel,
$queryData, $model->recursive);
                                }
                        }
                }

---> before
                // Build final query SQL

and add the methed mentioned:
  function __dal__generateRecursiveQuery(&$model, &$linkModel,
&$queryData, $recursive) {
        if ($recursive>0) {
                foreach($linkModel->__associations as $type) {
                        foreach($linkModel->{$type} as $assoc => $assocData) {
                                $newLinkModel =& $linkModel->{$assocData['className']};
                                $external = isset($assocData['external']);
                                $null=null;
                                if ($newLinkModel && $model->useDbConfig ==
$newLinkModel->useDbConfig) {
                                        $this->generateAssociationQuery($linkModel, $newLinkModel, $type,
$assoc, $assocData, $queryData, $external, $null);
                                        $this->__dal__generateRecursiveQuery($model,$newLinkModel,$queryData,$recursive-1);
                                }
                        }
                }
        }
  }

It gets better.  Now modify the REALLY weird code in the
__filterResults method
                                                if (isset($model->{$className}) &&
is_object($model->{$className})) {
                                                        $data = $model->{$className}->afterFind(array(array($key =>
$results[$i][$key])));
                                                } else {
                                                           //DAL: this
makes no sense.  The 'if' above proves it is not an object to call ...
                                                           //DAL:
$data = $model->{$className}->afterFind(array(array($key =>
$results[$i][$key])));


$data[0][$key]=& $results[$i][$key];
                                                }

AND IT WORKS!  But I get suspicious when I go modifying the core ....
One caveat to my solution is that the records are not getting filtered
by the recursively added classes.  I am sure this can be remedied if I
kept at it.  My question before I continue remains - do I need to do
this to do conditions on objects far away from the start?  My project
is migrating from a legacy system and this concept is predominant.

Sorry for the long post and many thanks in advance,

David


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Cake PHP" group.
To post to this group, send email to [hidden email]
To unsubscribe from this group, send email to [hidden email]
For more options, visit this group at http://groups.google.com/group/cake-php
-~----------~----~----~----~------~----~------~--~---

Reply | Threaded
Open this post in threaded view
|

Re: Fetching data with join over 3 tables with recursive association query

lloydhome

Well, it seems I have found the previously reported Trac #633.  The
partial workaround mentioned by elsigh is clumsy, messy, and may not
work for some implementations but has the advantage of actually working
without changing core code.

My implementation requires a proper recursion depth to be specified but
it automagically gets the query right.  The implementation proposed in
#633 may not require a recursion to be set but requires the path.  I
think the both have merit.  So the difference in my case is between


$this->User->findAll(array('Region.region_name','Canada'),null,null,null,1,3);
and

$this->User->findAll(array('Subarea.Area.Region.region_name','Canada'))

Can I get comments from 'those in the know' on which way this should be
implemented?  Is there work occurring on #633?  If so, I would like to
know the strategy so my code won't break.  If not, I can clean up my
implementation to work the way it should and submit it.

Thanks,
David


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Cake PHP" group.
To post to this group, send email to [hidden email]
To unsubscribe from this group, send email to [hidden email]
For more options, visit this group at http://groups.google.com/group/cake-php
-~----------~----~----~----~------~----~------~--~---