Nested hasOne relation with custom primary keys solved without using joins

Question

I have models: A [id, value], B [id, value] and X [id, a_id]. I want to find on X contain A and contain B where B.value = A.value, both A and B value fields are unique, so there is only one row relation (if any).

$this->X->find('all', array(
    'contain' => array(
        'A' => array('B')
    )
));

What i tried was $belongsTo association (on both side)

'B' => array(
    'foreignKey' => false,
    'conditions' => array(
        'A.value = B.value'
    )   
)

In sql log there is only 1=1 in WHERE section.

Is there a possibility to solve it without using joins, in single query?

Answer 1

You have to use

For cakephp 3.x

bindingKey: The name of the column in the current table, that will be used for matching the foreignKey. If not specified, the primary key (for example the id column of the Articles table) will be used.

As mentioned in http://book.cakephp.org/3.0/en/orm/associations.html

public $belongsTo = [
    'B' => [   
        'foreignKey' => 'status',
        'bindingKey'=>'status',
    ]
];

For cakephp 2.x

associationForeignKey: the name of the foreign key found in the other model. This is especially handy if you need to define multiple HABTM relationships. The default value for this key is the underscored, singular name of the other model, suffixed with ‘_id’.

As mentioned in http://book.cakephp.org/2.0/en/models/associations-linking-models-together.html

public $belongsTo = [
    'B' => [   
        'foreignKey' => 'status',
        'associationForeignKey'=>'status',
    ]
];

This will work for you

Also note that the default recursive level is 1 so you have to define the recursion to 2 as mention in this doc http://book.cakephp.org/2.0/en/models/model-attributes.html#recursive

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