Quick Tip - Doing Ad-hoc Joins in Model::find()

by nate
Herewith, a little-known query trick that allows you to do simple ad-hoc joins in your CakePHP finder queries. No binding or unbinding required.
Note: This only works if you are using the new Model::find() syntax, which only takes two parameters. If not, please refer to the Cookbook or API.

Part of the "zen" of CakePHP's design is the way in which things are layered. For example, many helper methods take an $options parameter, and methods which are built on top of other methods (think FormHelper or PaginatorHelper) allow you to pass in options at the top level that get passed down to the lower levels, giving you very granular control, even at high levels of abstraction.

So it is with the Model layer. All the options passed in Model::find() are simply handed off to DboSource for processing, which generates data that is directly embedded in the query's SQL. You can pass some of these options in from a higher level, specifically 'joins'.

One example I see commonly for this is searching tags, which are joined to a model via a hasAndBelongsToMany association. Typically, this is accomplished with model binding or raw query hacking. However, you can accomplish this just as easily using manual joins.

Right now at work, I'm involved in a project that integrates map-marking features, and one of the requirements is to be able to tag markers, and search those markers by tag. The search form provides a text field called q, that accepts a space-separated list of tag names, and is submitted via GET. Here is an example of the search code in MarkersController:

Controller Class:

<?php 
    $markers 
$this->Marker->find('all', array('joins' => array(
        array(
            
'table' => 'markers_tags',
            
'alias' => 'MarkersTag',
            
'type' => 'inner',
            
'foreignKey' => false,
            
'conditions'=> array('MarkersTag.marker_id = Marker.id')
        ),
        array(
            
'table' => 'tags',
            
'alias' => 'Tag',
            
'type' => 'inner',
            
'foreignKey' => false,
            
'conditions'=> array(
                
'Tag.id = MarkersTag.tag_id',
                
'Tag.tag' => explode(' '$this->params['url']['q'])
            )
        )
    )));
?>

We now have automatic filtering across an HABTM relationship. However, this requires quite a bit of code, and there are some redundancies in our implementation. Let's see if we can't refactor this such that it's a bit more reusable:

Model Class:

<?php 
class AppModel extends Model {

    public function 
find($type$options = array()) {
        if (!isset(
$options['joins'])) {
            
$options['joins'] = array();
        }

        switch (
$type) {
            case 
'matches':
                if (!isset(
$options['model']) || !isset($options['scope'])) {
                    break;
                }
                
$assoc $this->hasAndBelongsToMany[$options['model']];
                
$bind "{$assoc['with']}.{$assoc['foreignKey']} = {$this->alias}.{$this->primaryKey}";

                
$options['joins'][] = array(
                    
'table' => $assoc['joinTable'],
                    
'alias' => $assoc['with'],
                    
'type' => 'inner',
                    
'foreignKey' => false,
                    
'conditions'=> array($bind)
                );

                
$bind $options['model'] . '.' $this->{$options['model']}->primaryKey ' = ';
                
$bind .= "{$assoc['with']}.{$assoc['associationForeignKey']}";

                
$options['joins'][] = array(
                    
'table' => $this->{$options['model']}->table,
                    
'alias' => $options['model'],
                    
'type' => 'inner',
                    
'foreignKey' => false,
                    
'conditions'=> array($bind) + (array)$options['scope'],
                );
                unset(
$options['model'], $options['scope']);
                
$type 'all';
            break;
        }
        return 
parent::find($type$options);
    }
}
?>

Here, rather than simply hard-coding the joins for one association, we've abstracted the code and put it in AppModel, such that it now applies across any HABTM association and can be used with any field in the joined table. Before examining the code, let's take a look at the usage:

Controller Class:

<?php 
    $markers 
$this->Marker->find('matches', array(
        
'model' => 'Tag',
        
'scope' => array('Tag.tag' => explode(' '$this->params['url']['q']))
    ));
?>

Now, rather than building complicated joins in the controller, we have all our logic neatly tucked away, and need only specify the name of the model to be queried across, and the "scope" or filtering to be applied when joining. Since the joins are only used for searching and don't affect the field selection, the query results are returned in the same format as normal, and no additional data transfer overhead is incurred.

The rewritten method itself is actually quite simple when broken down. In each join, we're simply doing the job that Cake would normally do, and transferring array values from the available association info, to the appropriate places in the array that describes the join operation. In each instance, the $bind variable is used to generate the string that defines the foreign key relationship to the joined table. In each case as well, we're using INNER joins rather than LEFT, since we want any records not matching our criteria to be filtered out of the primary recordset.

Hopefully this sample will prove helpful in your work. If you have any questions or ideas, please leave them in the comments.

Report

More on Tutorials

Advertising

Comments

  • Imrael posted on 11/13/09 05:02:31 AM
    I put together the modified Find Method by Rob and the paginateCount by devmoz. Now I can search a Model for data related by HABTM and paginate the results.

    For anyone interested, I uploaded the code to CakeBin (http://bin.cakephp.org/view/361369389).

    I did not test it with nested relations though.
  • CraZyLeGs posted on 04/04/09 06:54:30 AM
  • nate posted on 03/13/09 11:36:00 AM
    Thanks for the feedback everyone. When I have time I'll post a follow-up on more types of joins, and using pagination.
  • bpechek posted on 02/25/09 03:58:31 PM
    Interesting, this is almost exactly what I do in my unpublished article “Cleaning up "on the fly" model bindings and getting deep DB relationships with one sql query” with my function superFind(). The way it works is different, and my function does not have a “scope” parameter, but mine can also join more than one model and join models not directly related to the model being queried. But the end result is nearly identical.
  • GrrrlRomeo posted on 02/20/09 11:23:38 AM
    It seems like paginated tag search results would be fairly common, yet I can't figure it out. I can get this tutorial to work but not with pagination. Or I can get the Custom Query Pagination to work but not with $this->params['url']. What am I missing?
  • byqsri posted on 02/20/09 03:55:17 AM
    Great.... I have two question
    1)Is it possible to do a similar thing for hasManu associations?
    2)If I have some conditions on different HABTM relations . How can I manage a similar situation?
    Many Thanks
    • BlueCube posted on 08/13/09 11:13:49 AM
      Great.... I have two question
      1)Is it possible to do a similar thing for hasManu associations?
      2)If I have some conditions on different HABTM relations . How can I manage a similar situation?
      Many Thanks

      In answer to question 2, if I've understood correctly you're in the same boat as me: I have an Advert model, with HABTM associations to both Area and Category. I need to search for Adverts by both Area and Category within the same find() operation. So I stole Bambou's code up there & added a hack to support multiple HABTM tables. I haven't tested this too rigorously but it does everything I need:

      Model Class:

      <?php 
          
      class AppModel extends Model
          
      {
              public function 
      find ($conditions null$options = array (), $order null$recursive null)
              {
                  if (
      is_string ($conditions))
                  {
                      switch (
      $conditions)
                      {
                          case 
      'matches':

                              if (!isset (
      $options['joins']))
                                  
      $options['joins'] = array ();

                              
      $conditions = isset ($options['operation']) ? $options['operation'] : 'all';

                              if (!isset (
      $options['model']) or !isset ($options['scope']))
                                  break;


                              
      // hack to filter over several HABTM tables

                              
      $model_list = (is_array ($options['model']) ? $options['model'] : array ($options['model']));

                              foreach (
      $model_list as $model)
                              {
                                  
      $scope = ((sizeof ($model_list) > and isset ($options['scope'][$model])) ? $options['scope'][$model] : $options['scope']);
                                  
      $assoc $this->hasAndBelongsToMany[$model];
                                  
      $bind "{$assoc['with']}.{$assoc['foreignKey']} = {$this->alias}.{$this->primaryKey}";

                                  
      $options['joins'][] = array (
                                      
      'table'         => $assoc['joinTable'],
                                      
      'alias'         => $assoc['with'],
                                      
      'type'          => 'inner',
                                      
      'foreignKey'    => false,
                                      
      'conditions'    => array ($bind)
                                  );

                                  
      $bind $model '.' $this->{$model}->primaryKey ' = ';
                                  
      $bind .= "{$assoc['with']}.{$assoc['associationForeignKey']}";

                                  
      $options['joins'][] = array (
                                      
      'table'         => $this->{$model}->table,
                                      
      'alias'         => $model,
                                      
      'type'          => 'inner',
                                      
      'foreignKey'    => false,
                                      
      'conditions'    => array_merge (array ($bind), (array) $scope)
                                  );
                              }

                              unset (
      $options['model'], $options['scope']);

                          break;
                      }
                  }

                  return 
      parent::find ($conditions$options$order$recursive);
              }
          }
      ?>

      Then you can filter either as per the original suggestion:

      Controller Class:

      <?php 
                  $search_results 
      $this->Advert->find ('matches', array (
                          
      'model'     => 'Area',
                          
      'scope'     => array ('Area.name LIKE'  => "%$area_name%")
                      )
                  );
      ?>

      Or over multiple tables:

      Controller Class:

      <?php 
                  $search_results 
      $this->Advert->find ('matches', array (
                          
      'model'     => array ('Area''Category'),
                          
      'scope'     => array (
                              
      'Area'      => array ('Area.name LIKE'     => "%$area_name%"),
                              
      'Category'  => array ('Category.name LIKE' => "%$category_name%")
                          )
                      )
                  );
      ?>

      Obviously the real search conditions are a bit more complex but they still work
      • Imrael posted on 12/10/09 10:03:08 AM
        Code by Rob:

        Controller Class:

        <?php 
                    $search_results 
        $this->Advert->find ('matches', array (
                            
        'model'     => array ('Area''Category'),
                            
        'scope'     => array (
                                
        'Area'      => array ('Area.name LIKE'     => "%$area_name%"),
                                
        'Category'  => array ('Category.name LIKE' => "%$category_name%")
                            )
                        )
                    );
        ?>

        I am having a litte problem with that.

        I have the following setup:
        Publication habtm Authors (Jointable authors_publications)
        Publication habtm ClassificationSchema (Jointable classification_schemas_publications)

        The Jointable of the ClassificationSchema Model holds some additional data (in this Case an actual value to classify a Publication against a ClassificationSchema), which is what I actually want my search result to be filtered against, i.e. Get all Publications from Author X, which are associated to the ClassificationSchema Y wich has the corresponding value for the association stored in the jointable.

        Somehow I can't get my code to resolve the fact, that I do not want to query the ClassificationSchema Model but the Join-Model.

        I tried to access the Join-Model directly, but when using Containable, the hasMany Relationship between Publication and ClassificationSchemasPublication won't be included in the Count and Paginate Statements, as hasMany is parsed later on.

        Any advice on this is very appreciated :)
  • jinhr posted on 02/19/09 01:47:13 PM
    to realize the function you presented.
    Any way, it is nice and flexible.
    • fraktal posted on 12/01/09 11:23:42 AM
      I've not tried your way, because it seemed too complicated to me (I'm not familiar with SQL joins), but I tried with the Containable behavior and it worked very well.

      I do a :
      $this->ModelA->find('first', array(
      'conditions' => [...],
      'recursive' => '2',
      'contain' => array('ModelB.ModelC.field')
      )));
  • gigapromoters posted on 02/07/09 05:33:24 AM
    I agree with you Nate, the new Cake design is rocking, and make things very much extensible just by adding functionality chunks based on $options array.
  • bambou713705 posted on 01/26/09 09:09:37 AM
    Hi Joe,

    Thanks to share that very useful code!

    Just one thing : your function override native Model::find but you don't use the same signature. This change Model::find behavior when it use in a different way than find('...', $options) and also change Model::read. For example, If you try $this->Model->read(null, $id) this will now return :

    Controller Class:

    <?php 
    array(
      
    => array(
         
    'field1' => ...
         ...
      )
    )
    ?>

    instead of directly :

    Controller Class:

    <?php 
    array (
      
    'field1' => ...
      ...
    )
    ?>


    I quickly wrote a fix:

    Model Class:

    <?php 

    class AppModel extends Model{

    // Fix signature for compatibility
        
    public function find($conditions null$options = array(), $order null$recursive null) {
    // Test if $conditions is a string, if not just call Parent::find
            
    if(is_string($conditions)){ 
    // Add here new find logics
                
    switch ($conditions) {
                    case 
    'matches':
                        if (!isset(
    $options['joins'])) {
                            
    $options['joins'] = array();
                        }
    // Hack : process 'operation' parameter to allow find('list/first/threaded/count/...', ...
                        
    $conditions = isset($options['operation']) ? $options['operation'] : 'all';
                        
                        if (!isset(
    $options['model']) || !isset($options['scope'])) {
                            break;
                        }
                        
    $assoc $this->hasAndBelongsToMany[$options['model']];
                        
    $bind "{$assoc['with']}.{$assoc['foreignKey']} = {$this->alias}.{$this->primaryKey}";
        
                        
    $options['joins'][] = array(
                            
    'table' => $assoc['joinTable'],
                            
    'alias' => $assoc['with'],
                            
    'type' => 'inner',
                            
    'foreignKey' => false,
                            
    'conditions'=> array($bind)
                        );
        
                        
    $bind $options['model'] . '.' $this->{$options['model']}->primaryKey ' = ';
                        
    $bind .= "{$assoc['with']}.{$assoc['associationForeignKey']}";
        
                        
    $options['joins'][] = array(
                        
    'table' => $this->{$options['model']}->table,
                            
    'alias' => $options['model'],
                            
    'type' => 'inner',
                            
    'foreignKey' => false,
                            
    'conditions'=> array($bind) + (array)$options['scope'],
                        );
                        unset(
    $options['model'], $options['scope']);
                        
                        break;
                }
            }
            return 
    parent::find($conditions$options$order$recursive);
        } 
    // End function
    // End class
    ?>


    In addition, I also add a 'operation' parameter to $options to change the find operation like that :

    Controller Class:

    <?php 
    // can now use first/list/all/...etc, default is 'all' when operation is not set
    $this->Model->find('matches', array(
           
    'operation' => 'threaded'
           ...
       )
    );
    ?>

    Thanks for your work!
  • joecritch89 posted on 01/24/09 05:35:12 AM

    This is a great, clean way of filtering HABTM results, well done.

    Can this be used in conjunction with $this->paginate(), if so, how can I do it?

    Cheers!

login to post a comment.