Model belongsTo another model several times issue

Need Help
1

Hello guys,

I need help form my model.

So I have Actions that belongsTo Users, classic use case, but it belongsTo this Users model 3 times, here’s my problem.

My Actions are created by a user, for another user, and validated by a third user, basically it’s an action workflow.

Here my ActionsTable :

class ActionsTable extends Table
{

    $this->belongsTo('FromUsers', [
        'foreignKey' => 'from_user_id',
        'joinType' => 'INNER',
        'className' => 'Users'
    ]);
    $this->belongsTo('ValidationUsers', [
        'foreignKey' => 'validation_user_id',
        'joinType' => 'INNER',
        'className' => 'Users'
    ]);
    $this->belongsTo('ToUsers', [
        'foreignKey' => 'to_user_id',
        'joinType' => 'INNER',
        'className' => 'Users'
    ]);

And Users belongsTo Customers

The problem is that when I have a query with for exemple ToUsers and FromUsers, I have this error :

Error: SQLSTATE[23000]: Integrity constraint violation: 1052 Champ: 'customer_id' dans on clause est ambigu

What am I doing wrong ?

Thanks in advance for your help !!

2

you have 3 relationships with same class so sql is lost and doesnt know which one you want to use, you need to specify it yourself, instead of ->where(['customer_id' => $id]) you need to tell it i.e. ->where(['ToUsers.customer_id' => $id])
in custom finders you can use

finderActive(Query $query)
{
   return $query->where([$this->getAlias() . '.status' => self::ACTIVE]);
}
3

Hello,

Thanks for your answer, the problem is that I don’t have any where clause, only the cakephp contain one, for example :

$actions = this->Actions->find()->contain(['ToUsers', 'FromUsers']);

-> Error

4

can you show what query does it generate?

the error could also be from automatically generated select fields statements but if you could provide sql query
$actions->sql() it would help to see where the error is coming from

5

Sure thanks for your help

$this->paginate = [
    'contain' => ['FromUsers', 'ToUsers']
];
$actions = $this->paginate($this->Actions);
$this->set(compact('actions'));

Error :

SELECT actions.id                 AS `Actions__id`, 
       actions.processing_id      AS `Actions__processing_id`, 
       actions.from_user_id       AS `Actions__from_user_id`, 
       actions.validation_user_id AS `Actions__validation_user_id`, 
       actions.to_user_id         AS `Actions__to_user_id`, 
       actions.name               AS `Actions__name`, 
       actions.description        AS `Actions__description`, 
       actions.action_priority_id AS `Actions__action_priority_id`, 
       actions.action_status_id   AS `Actions__action_status_id`, 
       actions.action_type_id     AS `Actions__action_type_id`, 
       actions.due_date           AS `Actions__due_date`, 
       actions.created            AS `Actions__created`, 
       actions.modified           AS `Actions__modified`, 
       FromUsers.id               AS `FromUsers__id`, 
       FromUsers.customer_id      AS `FromUsers__customer_id`, 
       FromUsers.first_name       AS `FromUsers__first_name`, 
       FromUsers.last_name        AS `FromUsers__last_name`, 
       FromUsers.email            AS `FromUsers__email`, 
       FromUsers.password         AS `FromUsers__password`, 
       FromUsers.role             AS `FromUsers__role`, 
       FromUsers.address          AS `FromUsers__address`, 
       FromUsers.postcode         AS `FromUsers__postcode`, 
       FromUsers.city             AS `FromUsers__city`, 
       FromUsers.country          AS `FromUsers__country`, 
       FromUsers.phone            AS `FromUsers__phone`, 
       FromUsers.job              AS `FromUsers__job`, 
       FromUsers.avatar           AS `FromUsers__avatar`, 
       FromUsers.company_id       AS `FromUsers__company_id`, 
       FromUsers.created          AS `FromUsers__created`, 
       FromUsers.modified         AS `FromUsers__modified`, 
       ToUsers.id                 AS `ToUsers__id`, 
       ToUsers.customer_id        AS `ToUsers__customer_id`, 
       ToUsers.first_name         AS `ToUsers__first_name`, 
       ToUsers.last_name          AS `ToUsers__last_name`, 
       ToUsers.email              AS `ToUsers__email`, 
       ToUsers.password           AS `ToUsers__password`, 
       ToUsers.role               AS `ToUsers__role`, 
       ToUsers.address            AS `ToUsers__address`, 
       ToUsers.postcode           AS `ToUsers__postcode`, 
       ToUsers.city               AS `ToUsers__city`, 
       ToUsers.country            AS `ToUsers__country`, 
       ToUsers.phone              AS `ToUsers__phone`, 
       ToUsers.job                AS `ToUsers__job`, 
       ToUsers.avatar             AS `ToUsers__avatar`, 
       ToUsers.company_id         AS `ToUsers__company_id`, 
       ToUsers.created            AS `ToUsers__created`, 
       ToUsers.modified           AS `ToUsers__modified` 
FROM   actions Actions 
       INNER JOIN users FromUsers 
               ON ( FromUsers.id = ( actions.from_user_id ) 
                    AND customer_id = :c0 ) 
       INNER JOIN users ToUsers 
               ON ( ToUsers.id = ( actions.to_user_id ) 
                    AND customer_id = :c1 ) 
LIMIT  20 offset 0
6

this is the source of error:
AND customer_id = :c0 ) and AND customer_id = :c1 ) but its not from code you posted, maybe you have behavior that appeds it? or some custom logic in event listeners?

7

Haaaaaa you’re right !

My application has a SaaS model, Users belongsTo a Customer, and I have this in my UsersTable

public function beforeFind($event, $query, $options, $primary) {
    $query->where(['customer_id' => $this->request->session()->read('Auth.User.customer_id')]);
}

Several customers share the same database.

I should prefix with the Alias isn’t it ?

EDIT : FIXED !!

Solution :

public function beforeFind($event, $query, $options, $primary) {
    $query->where([$this->aliasField('customer_id') => $this->request->session()->read('Auth.User.customer_id')]);
}

Thanks a lot !

1 Like
8

no problem i didnt even know there is this method aliasField : )