Table binding in Model :: find ()

Translation notes 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 technique that allows you to join tables (joins) in CakePHP queries directly, without using the bind and unbind methods.

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

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

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

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

<?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 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 gestures. Let's see if you can rework the code so that it is more convenient for later use.

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

So instead of rigidly defining connections for one association, we make the code more universal and place it inside the AppModel. Thus, we can use it for any many-to-many relationship and any field of a related table.
Before we figure out how the code works, let's see how to use it:

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

Now, 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 entangled links in the controller. Since the specified filtering is used only for searching strings and does not affect the list of requested fields, data is returned in the same format as usual, without transferring unnecessary information.

The method we have reworked is very simple if you look at it. For each link, we do what 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 foreign key relationship for the tables being linked. 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 will help you in your work. If you have any questions or comments - write.