When we are querying a model that has other associated models, CakePHP will issue a new query to fetch the associated data, or use a LEFT JOIN SQL statement if the associated model has a one-to-one relationship with the main model (through a binding defined with belongsTo or hasOne.)
However there are times where we need to change the join type for one-to-one associations, to use either a RIGHT JOIN or an INNER JOIN. This recipe shows us how to change the join type for belongsTo and hasOne associations.
Follow the Getting ready section of the recipe, Limiting the bindings returned in a find.
- Edit the
Personmodel, and change the binding definitions forbelongsToandhasOneassociations, as shown below:<?php class Person extends AppModel { public $belongsTo = array('Family' => array('type' => 'INNER')); public $hasOne = array('Profile' => array('type' => 'RIGHT')); public $hasMany = array('Post'), } ?>
When we add bindings to a model, we can pass an array of settings to the binding definition to configure different aspects of the binding. One of those settings is type, only applicable to belongsTo and hasOne bindings.
The type setting allows us to define what type of JOIN CakePHP will use when fetching the associated model (only when querying the main model.) The available JOIN types are:
INNER JOIN: Joins and only returns records from associated models that match the default join condition. When a binding is set to use this join type, only records that have a record for the binding will be returned. In the example above, onlyPersonrecords that belong to aFamilywill be returned.LEFT JOIN: This is the default join type used by CakePHP. All records are returned even if there is no record for the binding. In the example above, if theFamilybinding type is set toLEFT, thenPersonrecords will be returned even if they don't belong to aFamily.RIGHT JOIN: The opposite ofLEFT JOIN, shows all records from the related model even if they are not related to the main model, and shows only records in the main model that are linked to the related model.