How to fetch deep associations with CakePHP's find operations?

Question

I am building a website that has three models: Store, StoreReview, and User. The website lists stores, and users can review stores. Therefore, my Store model can have many StoreReview rows, and each StoreReview row belongs to a User row.

My question is: when fetching all Store rows, how can I also fetch the relevant User along with the StoreReview rows? Currently I'm using:

<?php
class StoresController extends AppController {

    public function view($slug) {
        $stores = $this->Store->find('all');
    }
}

But this only returns 'StoreReview' rows. Because the User row is another level deeper, I'm unsure how to fetch this; I've checked CakePHP's documentation and Googled, but CakePHP have recently re-jigged their documentation site, and the samples on websites from my Google search didn't work.

Thank you in advance.

Answer 1

There are two ways. You can increase the recursive attribute:

$stores = $this->Store->find('all', array('recursive' => 2));

Or use the Containable behavior (I prefer this since you can make more than 2 levels):

$this->Store->Behaviors->attach('Containable');
$this->Store->contain(array('StoreReview' => array('User')));
$stores = $this->Store->find('all');
$this->Store->Behaviors->detach('Containable');

More informations:

• recursive
• Containable

Answer 2

I wrote a function similar to containable to do deep queries.

The data gets returned in the same format as you would expect from containable.

It could be useful if you need to add additional search criteria to sub levels of your query.

In short the function allows you to drill as deep as you want without returning all of the superfluous junk but gives you extra control over what happens between calls.

Containable is awesome but does not call behaviors and other functions you might need to call inbetween.

Example: I need to run an acl filter on the returned data after each call. Could not find a way to do this with containable.

Now it is easy to add additional function calls to the beginning and end of this script as pre and post call actions.

Just plug it into your AppModel and watch the magic happen.

Here is the an example of how a call would look:

$options = ['conditions'=>['User.id'=>9]];
$options['drill'] => ['Relation1', 'Relation2'=>['drill'=>['Subrelation1', 'Subrelation2'=>['drill'=>['ThirdLevelRelation']]]]];
$this->request('find', $options);

Here is how to add options to the sub relations.

$options['drill'] => ['Relation1', 'Relation2'=>['fields'=>['field_a','field_b'], 'conditions'=>['alias'=>'test'], 'drill'=>[....] ]];

Here goes: ( PS. php5.4+ you can replace array syntax if using older version of php)

  public function request($method='first', $options=array()){
    $result = $this->find($method, $options);
    if(isset($options['drill'])){
      $bits = $bitOptions = $subDrils = $subBits = [];
      if(is_array($options['drill'])){
        foreach($options['drill'] as $key => $val){
          if(is_array($val)){
            $bits[]=$key;
            $bitOptions[$key] = $val;
            if(isset($val['drill'])){
              if(is_array($val['drill'])){
                foreach($val['drill'] as $sKey => $sVal){
                  $subBits[$key][] = is_array($sVal)?$sKey:$sVal;
                }
              } else {
                $subBits[$key][] = $val['drill'];
              }
            }
          } else {
            $bits[] = $val;
          }
        }
      } else {
        $bits[] = $options['drill'];
      }
      foreach($bits as $bit){
        if(isset($gems)){unset($gems);$gems=[];}
        if(isset($result[$bit])){
          $gems[] =& $result[$bit];
        } else {
          foreach($result as $k => $v){
            if(isset($result[$k][$bit])){
              $gems[] =& $result[$k][$bit];
            }
          }
        }
        foreach($gems as $key => $gem){
          if(!empty($gem)){
            $m = $this->$bit;
            if(is_object($m)){
              foreach(['hasOne','belongsTo','hasMany','hasAndBelongsToMany'] as $relation){
                foreach(array_keys($m->$relation) as $alias){
                  if(isset($subBits[$bit])){
                    if(!in_array($alias, $subBits[$bit])){
                      $m->unBindModel([$relation=>[$alias]]); 
                    }
                  }
                }
              }
              if(!empty($subBits[$bit])){
                $opts = isset($bitOptions[$bit])?$bitOptions[$bit]:[];
                if(isset($gem[$m->primaryKey])){
                  if(!isset($opts['conditions'])){
                    $opts['conditions'] = [$m->alias.'.'.$m->primaryKey=>$gem[$m->primaryKey]];
                  } else {
                    $opts['conditions'] = Hash::merge($opts['conditions'], [$m->alias.'.'.$m->primaryKey=>$gem[$m->primaryKey]]);
                  }
                  if($r = $m->request('first', $opts)){
                    unset($r[$m->alias]);
                    $gems[$key] = Hash::merge($gems[$key], $r); 
                  }
                } else {
                  reset($gem);
                  $first_key = key($gem);
                  $first = $gem[$first_key];
                  if(isset($first[$m->primaryKey])){
                    foreach($gem as $gemKey => $gemVal){
                      if(!isset($opts['conditions'])){
                        $opts['conditions'] = [$m->alias.'.'.$m->primaryKey=>$gemVal[$m->primaryKey]];
                      } else {
                        $opts['conditions'] = Hash::merge($opts['conditions'], [$m->alias.'.'.$m->primaryKey=>$gemVal[$m->primaryKey]]);
                      }
                      if(isset($opts['method'])){
                        $method = $opts['method'];
                      } else {
                        $method = 'first';
                      }
                      if($r = $m->request('first', $opts)){
                        unset($r[$m->alias]);
                        $gems[$key][$gemKey] = Hash::merge($gems[$key][$gemKey], $r);
                      }
                    }
                  }
                } 
              }
            }
          }
        }
      }
    }
  }

Wrote a helper function to make calling this function a bit cleaner:

The query would now look something like this:

$this->Model->find('all', ['drill'=>$this->Model->drill(['Assoc1'=>['SubAssoc1','SubAssoc2'=>['o'=>['conditions'=>'condition', 'fields'=>['fielda', 'fieldb', 'fieldc']], 'SubAssoc3' ]]])]);

Helper:

  public function drill($array=array(), $first=true){
$drill = [];
foreach($array as $key => $value){
  if(is_array($value)){
    if(isset($value['o']) && is_array($value['o'])){
      foreach($value['o'] as $k => $v){
        $drill['drill'][$key][$k] = $v;
      }
      unset($value['o']);
    }           
    if(!empty($value)){
      if(isset($drill['drill'][$key])){
        $drill['drill'][$key] = Hash::merge($drill['drill'][$key],$this->drill($value, false));
      } else {
        $drill['drill'][$key] = $this->drill($value, false);
      }
    }
  } else {     
    $drill['drill'][] = $value;
  }
}
if($first){
  return $drill['drill'];
}
return $drill;

}