Bidirectional belongsToMany association on same model

Question

This EntitiesTable has a belongsToMany association with itself:

class EntitiesTable extends AppTable
{
    public function initialize(array $config) {
        parent::initialize($config);
        
        $this->belongsTo('Users');
        $this->hasMany('Information');
        $this->belongsToMany('LinkedEntities', array(
            'className' => 'Entities',
            'through' => 'Links',
            'foreignKey' => 'from_id',
            'targetForeignKey' => 'to_id'
        ));
        $this->belongsToMany('Tags');
    }
}

However, this association only works in one direction. When the table links only contains an entry [from_id: 1, to_id: 2], $this->Entities->findById(1)->contain('LinkedEntities') correctly fetches the linked entity, but $this->Entities->findById(2)->contain('LinkedEntities') does not.

One solution would be to duplicate every entry in the table links and swap from_id and to_id, but this is not desirable. I much rather would like a belongsToMany association considering from_id as foreignKey and to_id as targetForeignKey and vice versa.

How can a bidirectional belongsToMany self-association be implemented?

An Example

Consider the following graph-like network:

Each dot has an id and maybe some additional information (like a title or coordinates) and is stored in the table entities.

Now the connections between the dots can be represented using a table links:

from_id     to_id
1           2
1           4
1           6
2           3
3           5
3           7
4           5
4           6
5           7

Note that each connection only is present once, 1-2 could just as well be stored as 2-1, the direction from-to does not matter.

Now, if I want to get all the nodes connected to node 1, I could use the following query:

SELECT * FROM entities WHERE id IN (
    SELECT to_id FROM links WHERE from_id = 1
) OR id IN (
    SELECT from_id FROM links WHERE to_id = 1
)

See how easy that was? I just have to make sure to check both directions, as the connection is bi-directional.

I want this association mapped in CakePHP. I know this might very well be currently impossible, which is why I opened an issue in the CakePHP github.

Related question: http://stackoverflow.com/q/20832098/2580794 The solution there only works for older versions of CakePHP, since the option `finderQuery` was removed. — Lars Ebert, Oct 06 '15 at 14:57
Try to find Entities by $id, and add conditions to containable LinkedEntities WHERE is from_id = $id OR to_id = $id http://book.cakephp.org/3.0/en/orm/retrieving-data-and-resultsets.html#passing-conditions-to-contain — Salines, Oct 06 '15 at 17:16
Adding additional conditions will only limit the linked Entities further, since the resulting query would still require to_id to be $id. — Lars Ebert, Oct 06 '15 at 17:38
I think you will need to create two associations: LinkedToEntities and LinkedFromEntities. — Greg Schmidt, Oct 07 '15 at 00:41
Maybe the foreignKeys are named a bit badly. There actually is no from and to in the association, from_id and to_id are meant to be interchangable. There is no logical reason for some linked entities to be in one or the other association except CakePHP does not seem to handle this case. — Lars Ebert, Oct 07 '15 at 03:59

score 0 · Answer 1 · answered Oct 11 '15 at 10:22

Does this help?

$this->belongsToMany('LinkedEntities', [
    'className' => 'Entities',
    'through' => 'links',
    'conditions' => [
         'OR' => [
                'AND' =>['Links.from_id = LinkedEntities.id', 'Links.to_id = Entities.id'],
                'AND' => ['Links.to_id = LinkedEntities.id', 'Links.from_id = Entities.id']
            ],
    ],
]);

Place this inside the EntitiesTable

Bidirectional belongsToMany association on same model

An Example

1 Answers1