How to create a hasOne association that joins on multiple foreign keys via an OR condition?

Question

I'm trying to join a table (Connections) twice from another table (Ports).

The conditions are:

One port can have one or no connection
One connection has one from-port and one to-port

The structure is:

Table Ports:

id
number

Table Connections:

id
port_from_id
port_to_id
name

So now I want to join from Ports to Connections.

Cake always gives me this:

LEFT JOIN connections Connections ON Ports.id = (Connections.port_from_id)

But it has to be:

LEFT JOIN connections Connections ON Ports.id = (Connections.port_from_id) OR Ports.id = (Connections.port_to_id)

or something equivalent.

My Ports-Model actually looks like this:

$this->hasOne('Connections', [
            'foreignKey' => 'port_from_id',
            'joinType' => 'LEFT'
        ]);

How can I get the OR-Condition in my join?

Thanks!

score 0 · Accepted Answer · answered Feb 23 '18 at 16:43

You'd have to disable the associations foreign key handling (IIRC this only works for hasOne associations using the JOIN strategy, it might work for belongsTo too), and supply the conditions on your own, as multiple foreign keys aren't supported (unlike composite foreign keys).

Something along the lines of:

use Cake\Database\Expression\IdentifierExpression;

// ...

$this->hasOne('Connections', [
    'foreignKey' => false,
    'conditions' => [
        'OR' => [
            'Connections.port_from_id' => new IdentifierExpression($this->aliasField('id')),
            'Connections.port_to_id' => new IdentifierExpression($this->aliasField('id')),
        ]
    ]
]);

That should create SQL similar to:

LEFT JOIN
    `connections` `Connections` ON (
        `Connections`.`port_from_id` = (`Ports`.`id`) OR
        `Connections`.`port_to_id` = (`Ports`.`id`)
    )

How to create a hasOne association that joins on multiple foreign keys via an OR condition?

1 Answers1