Reflexive many-to-many relationship in CakePHP

Question

I've been searching around for a definitive answer to this issue but have been unable to resolve it. I've recently been working with (and learning) CakePHP and have run across an obstacle. To simplify my database, let's say we simply have two tables:

persons relationships (or persons_persons)

Persons has a many-to-many relationship with itself - and indeed, two people can have more than one relationship with each other:

persons
-------
*person_id*
name

relationships
-------------
*relationship_id*
person_1_id
person_2_id
start_date
end_date

Now, if I want to say Person1 (id=1) is married to Person2, I would have one entry in the relationships table. person_1_id would be 1, and person_2_id would be two.

I can create this relationship in CakePHP, and show the lists of relationships (and persons) per Person record. However, it's a unidirectional relationship: for Person 1, the query will only pull Relationship objects where person_1_id matches. If I want to query the relationships for Person 2, I'd have to have a second identical row, with the person_1_id and person_2_id swapped.

Here's the models:

class Member extends AppModel {
    public $hasMany = array(
        'Relationships' => array(
            'className' => 'Relationship',
            'foreignKey' => 'person_1_id'
        )
    );
}

class Relationship extends AppModel {
    public $belongsTo = array(
        'Person1' => array(
            'className' => 'Person',
            'foreignKey' => 'person_1_id'
        ),
        'Person2' => array(
            'className' => 'Person',
            'foreignKey' => 'person_2_id'
        )
    );
}

Any suggestions? It doesn't make logical sense to duplicate Relationship entities when person_1_id is actually no separate than person_2_id.

Answer 1

I think you've got two options:

1. Duplicate the Relationship Record

Look at it from a different perspective; Consider the concept of directed vs. an un-directed graph. Your current schema supports a directed edge leaving one object (Person) and arriving at another. Agreed from your application's perspective, a person can't have a relationship with another, without them knowing.

The amount of space within the database would be negligible if you did choose to go down this route and would be less complex to implement than option 2. Bare in mind that you should put in place measures to ensure the relationship remains symmetrical at all times.

2. Handle the Relationship Asymmetry in Your Application

Create another relationship so your model looks like the following:

class Member extends AppModel {
    public $hasMany = array(
        'RelationshipsA' => array(
            'className' => 'Relationship',
            'foreignKey' => 'person_1_id'
        ),
        'RelationshipsB' => array(
            'className' => 'Relationship',
            'foreignKey' => 'person_2_id'
        )
    );
}

You then have separate bins for your relationships depending on which side of the relationship your person exists. You then have to put extra logic everywhere so that you can handle the relationship bins as one homogeneous collection.

I'm sorry I couldn't be any more helpful but I can't think off the top my head, how to implement an un-directed graph without building it on top of a directed one.

Update 05/05/13

To summarize the comment thread, setting the finderQuery on the hasMany association can mask the need to duplicate the lookup for the association with person_2_id (Documentation):

'Relationship' => array(
    'finderQuery' => 'SELECT Relationship.* FROM relationships AS Relationship WHERE Relationship.person_2_id = {$__cakeID__$} OR Relationship.person_1_id = {$__cakeID__$};'
)

This hides the complex retrieval mechanism but storing relationships may require further thought.