Multiple associations to same table

Need Help
1

Greetings all. I have this database schema:

create table locations(
        id bigint(20) unsigned not null auto_increment primary key,
	...	
        user_id bigint(20) unsigned not null references users(id)
);

create table couplings(
        id bigint(20) unsigned not null auto_increment primary key,
	...
        location_id bigint(20) unsigned not null references locations(id)
);

create table coupling_actions(
        id bigint(20) unsigned not null auto_increment primary key,
	...
        coupling_id bigint(20) unsigned not null references couplings(id)
);

alter table locations add coupling_action_off bigint(20) unsigned default null references coupling_actions(id);
alter table locations add coupling_action_normal bigint(20) unsigned default null references coupling_actions(id);

CouplingAction thus belongs to a Coupling which in turn belongs to a Location. However, Location should have two specially selected CouplingActions “Off” and “Normal”.

Entities are as follows:

/**
 * Location Entity
 *
 * @property int|null $coupling_action_off
 * @property int|null $coupling_action_normal
 *
 * @property \App\Model\Entity\CouplingAction $coupling_action_off
 * @property \App\Model\Entity\CouplingAction $coupling_action_normal
 */
class Location extends Entity
{
    protected $_accessible = [
        'coupling_action_off' => true,
        'coupling_action_normal' => true,
    ];
    
}


/**
 * CouplingAction Entity
 *
 * @property int $coupling_id
 *
 * @property \App\Model\Entity\Coupling $coupling
 * @property \App\Model\Entity\Location[] $locations
 */
class CouplingAction extends Entity
{
    protected $_accessible = [
        'coupling_id' => true,
        'coupling' => true,
        'locations' => true,
    ];
}

Tables

/**
 * Locations Model
 * 
 * @property \App\Model\Table\CouplingActionsTable&\Cake\ORM\Association\BelongsTo $CouplingActions
 * @property \App\Model\Table\CouplingsTable&\Cake\ORM\Association\HasMany $Couplings
 *
 */
class LocationsTable extends Table
{
    public function initialize(array $config): void
    {
        parent::initialize($config);

        $this->setTable('locations');
        $this->setDisplayField('id');
        $this->setPrimaryKey('id');

        $this->belongsTo('CouplingActions', [
            'className' => 'CouplingActions',
            'propertyName' => 'coupling_action_off',
            'foreignKey' => 'coupling_action_off',
            'joinType' => 'INNER',
        ]);
        $this->belongsTo('CouplingActions', [
            'className' => 'CouplingActions',
            'propertyName' => 'coupling_action_normal',
            'foreignKey' => 'coupling_action_normal',
            'joinType' => 'INNER',
        ]);
     }
}

/**
 * CouplingActions Model
 *
 * @property \App\Model\Table\CouplingsTable&\Cake\ORM\Association\BelongsTo $Couplings
 * @property \App\Model\Table\LocationsTable&\Cake\ORM\Association\HasMany $Locations
 *
 */
class CouplingActionsTable extends Table
{
    public function initialize(array $config): void
    {
        parent::initialize($config);

        $this->setTable('coupling_actions');
        $this->setDisplayField('id');
        $this->setPrimaryKey('id');

        $this->belongsTo('Couplings', [
            'foreignKey' => 'coupling_id',
            'joinType' => 'INNER',
        ]);
        $this->hasMany('Locations', [
            //'className' => 'Locations',
            //'propertyName' => 'coupling_action_off',
            'foreignKey' => 'coupling_action_off'
        ]);
        $this->hasMany('Locations', [
            //'className' => 'Locations',
            //'propertyName' => 'coupling_action_normal',
            'foreignKey' => 'coupling_action_normal'
        ]);
     }
 }

This Controller test call…

    $location = $this->Locations->get($id, [
           'contain' => ['Couplings.CouplingActions'],
     ]);

… returns a Location object with correct id values, but not the entities.

App\Model\Entity\Location Object
(
    [id] => 1
    [identifier] => Test Identifier
    [description] => Test Desc
    [coordinates] => 12.3456,23.4567
    [user_id] => 1
    [coupling_action_off] => 1
    [coupling_action_normal] => 2
    [heatingcurve_id] => 1
    [couplings] => Array
        (
            [0] => App\Model\Entity\Coupling Object
...

I have tried with different className and propertyName, but I bet I have made some stupid misconfiguration which I cannot find. Any help would be highly appreciated.

2

The problem is you are setting up 2 hasMany association with the same alias Locations. You need to use different aliases. So something like:

        $this->hasMany('LocationsOff', [
            'className' => 'Locations',
            'foreignKey' => 'coupling_action_off'
        ]);
        $this->hasMany('Locations', [
            'className' => 'Locations',
            'foreignKey' => 'coupling_action_normal'
        ]);
1 Like