Table binding in Model :: find ()

Translation of a note on Bakery by Nate, one of the authors of CakePHP. It seemed to me interesting and illustrating how this framework works, but difficult to understand in English.

This note describes a little-known trick that allows you to bind tables (joins) in CakePHP queries directly, without using the bind and unbind methods.

Note: The method will only work if you use the new Model :: find () syntax, which has only two parameters. Otherwise, read the Cookbook or API.


One of the “tricks” of CakePHP design is “layering”. For example: many Helper methods accept the $ options parameter, and methods built on their basis (see FormHelper or PaginatorHelper methods) allow you to pass some settings to lower levels, providing the ability to fine-tune even from high levels of abstraction.

So it is with models: all parameters passed to Model :: find () are then passed for processing to DboSource, which is responsible for generating SQL queries. That is, you can pass some parameters, in particular the joins parameter, from a higher level to DboSource.

One of the most common examples is to search by tags that relate to the model as many-to-many (hasAndBelongsToMany). This is usually achieved by binding models or writing a query manually. But the same result can be achieved simply by describing the connection using the “joins” parameter.
At the moment I am working on one project that allows you to leave marks on the map and search for marks by their tags. In the tag search form sent by the get method, there is a q text field that accepts space-separated tags. Here is sample code to search in MarkersController:

    $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 get automatic filtering in the many-to-many relationship. However, for this you will have to write quite a lot of code and make a lot of extra body movements. Let's see if it is possible to rework the code so that it is more convenient for future use.

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);
    }
}?>

So, instead of hard-coded links for one association, we make the code more universal and put it inside the AppModel. Thus, we can use it for any many-to-many relationship and any field of a linked table.
Before we figure out how the code works, let's see how to use it:

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

Now, by carefully “hiding” the logic in the model, we only indicate the name of the associated model and the filtering criteria when linking instead of building tangled links in the controller. Since the specified filtering is used only for searching strings and does not affect the list of requested fields, the data is returned in the same format as usual, without transferring unnecessary information.

The method we have revised is very simple, if you look at it. For each link, we do the same as CakePHP usually does, using the associative links specified for the model - we fill in the “joins” parameter. The $ bind variable is used to form a string that defines the relation of foreign keys for linked tables. We also use INNER JOIN instead of LEFT JOIN, since we do not need records that do not meet our filtering criteria.

I hope this advice helps you in your work. If you have any questions or comments - write.