Cakephp 3.9 Multiple Associations with the Same Table

suhaz619 · September 29, 2020, 10:26am

I am working on Cakephp 3.9 and I have 3 tables in my application.

CREATE TABLE `leads` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `first_name` varchar(100) NOT NULL,
 `last_name` varchar(100) NOT NULL,
 `created_dt` datetime NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

CREATE TABLE `clients` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `lead_id` int(10) NOT NULL,
 `first_name` varchar(100) NOT NULL,
 `last_name` varchar(100) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

CREATE TABLE `contacts` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `lead_id` int(10) NOT NULL,
 `first_name` varchar(100) NOT NULL,
 `last_name` varchar(100) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

There is a one-to-one relation between the Leads, Clients, and Contacts tables.

I also have a Users table:

CREATE TABLE `users` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `username` varchar(50) DEFAULT NULL,
 `password` varchar(255) DEFAULT NULL,
 `created` datetime DEFAULT NULL,
 `modified` datetime DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

… and a leads_users table

CREATE TABLE `leads_users` (
 `lead_id` int(10) NOT NULL,
 `user_id` int(10) NOT NULL,
 `user_type` enum('Opener','Closer') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

In my application, the users can be associated with the leads as Openers and Closers. There can be multiple openers and multiple closers for a lead. I have defined my models as below:

class LeadsTable extends Table
{
    public function initialize(array $config)
    {
        $this->hasOne('Contacts');

        $this->hasOne('Clients');

        $this->belongsToMany('Users', [
            'className' => 'Users'
        ])
            ->setConditions(['LeadsUsers.user_type' => 'Opener'])
            ->setProperty('openers');

        $this->belongsToMany('Users', [
            'className' => 'Users'
        ])
            ->setConditions(['LeadsUsers.user_type' => 'Closer'])
            ->setProperty('closers');
    }
}

class ClientsTable extends Table
{
    public function initialize(array $config)
    {
        $this->belongsTo('Leads');
    }

}

class ContactsTable extends Table
{
    public function initialize(array $config)
    {
        $this->belongsTo('Leads');
    }

}

class UsersTable extends Table
{
    public function initialize(array $config)
    {
        $this->belongsToMany('Leads');
    }

}

Now in the Leads controller, I am trying to find all the leads, their contacts, clients, openers, and closers by using the below line of code.

$result = $this->Leads->find('all')->contain(['Contacts', 'Clients', 'Users']);

With this, I am getting the closers but not the openers. In the LeadsTable, if I comment out the closers association then I am getting the openers. So looks like the second association is overwriting the first one.

If I change the LeadsTable association to below:

class LeadsTable extends Table
{
    public function initialize(array $config)
    {
        $this->hasOne('Contacts');

        $this->hasOne('Clients');

        $this->belongsToMany('Openers', [
            'className' => 'Users'
        ])
            ->setConditions(['LeadsUsers.user_type' => 'Opener'])
            ->setProperty('openers');

        $this->belongsToMany('Closers', [
            'className' => 'Users'
        ])
            ->setConditions(['LeadsUsers.user_type' => 'Closer'])
            ->setProperty('closers');
    }

}

I am getting the below error:

The Users association is not defined on Leads.

Please let me know what am I doing wrong. Thank you in advance.

Zuluru · September 29, 2020, 1:50pm

Your last initialize is right, where you have separate Openers and Closers associations. But then you need to contain Openers and Closers, not Users.

suhaz619 · September 29, 2020, 2:01pm

Thank you Zularu for your reply.

I tried that,

$result = $this->Leads->find('all')->contain(['Contacts', 'Clients', 'Closers','Openers']);

But then I get below error.

**Error:** SQLSTATE[42S22]: Column not found: 1054 Unknown column 'LeadsUsers.closer_id' in 'on clause'

SELECT LeadsUsers.lead_id AS Closers_CJoin__lead_id, LeadsUsers.user_id AS Closers_CJoin__user_id, LeadsUsers.user_type AS Closers_CJoin__user_type, Closers.id AS Closers__id, Closers.username AS Closers__username, Closers.password AS Closers__password, Closers.role AS Closers__role, Closers.created AS Closers__created, Closers.modified AS Closers__modified FROM users Closers INNER JOIN leads_users LeadsUsers ON Closers.id = (LeadsUsers.closer_id) WHERE (LeadsUsers.lead_id in (:c0,:c1) AND LeadsUsers.user_type = :c2)

It is assuming a new join column in the query closer_id. Do you think I need to specify two belongsTo in the Users model? Currently I have just one:

public function initialize(array $config)
	{
		$this->belongsToMany('Leads');
	}

Do I need to somehow label this as an opener and create another and name it as a closer?

Zuluru · September 29, 2020, 3:40pm

Since you’re using a table name that doesn’t match the convention (by necessity, in this case), there are other fields you’ll likely have to set in the association definition. Foreign key, for example. I don’t have a code example right at hand.

suhaz619 · September 29, 2020, 8:11pm

I tried adding that and this is how my LeadsTable look now:

public function initialize(array $config)
{
$this->hasOne(‘Contacts’);

	$this->hasOne('Clients');

	$this->belongsToMany('openers', [
		'className' => 'Users',
		'foreignKey' => 'lead_id',
		'bindingKey' => 'user_id',
	])
		->setConditions(['LeadsUsers.user_type' => 'Opener'])
		->setProperty('openers');

	$this->belongsToMany('closers', [
		'className' => 'Users',
		'foreignKey' => 'lead_id',
		'bindingKey' => 'user_id',
	])
		->setConditions(['LeadsUsers.user_type' => 'Closer'])
		->setProperty('closers');
}

Now I see the openers and closers in the output, but those are blank. In the debug query log, I just see one query:

SELECT 
  Leads.id AS `Leads__id`, 
  Leads.first_name AS `Leads__first_name`, 
  Leads.last_name AS `Leads__last_name`, 
  Leads.created_dt AS `Leads__created_dt`, 
  Clients.id AS `Clients__id`, 
  Clients.lead_id AS `Clients__lead_id`, 
  Clients.first_name AS `Clients__first_name`, 
  Clients.last_name AS `Clients__last_name`, 
  Contacts.id AS `Contacts__id`, 
  Contacts.lead_id AS `Contacts__lead_id`, 
  Contacts.first_name AS `Contacts__first_name`, 
  Contacts.last_name AS `Contacts__last_name` 
FROM 
  leads Leads 
  LEFT JOIN clients Clients ON Leads.id = (Clients.lead_id) 
  LEFT JOIN contacts Contacts ON Leads.id = (Contacts.lead_id)

The openers and closers associations SQL is not even triggered.

suhaz619 · September 29, 2020, 8:39pm

It’s working only for one association with the Users table. The moment I add another “contain” with the same table, it either throws an error, overwrites the previous contain data, or show both as blank.

Zuluru · September 29, 2020, 9:12pm

$this->belongsToMany('openers' should be $this->belongsToMany('Openers' (note the upper case). This was right previously and got changed? Not sure if this is the source of this problem, but will presumably cause others if it’s not.

suhaz619 · September 29, 2020, 9:32pm

src/Controller/LeadsController.php

<?php

    namespace App\Controller;

    use App\Controller\AppController;

    class LeadsController extends AppController {

    	public function initialize() {
    		parent::initialize();
    //		$this->viewBuilder()->setLayout("default");
    		$this->loadModel('Leads');
    	}

    	public function index() {
    		$result = $this->Leads->find()->contain(['Openers', 'Closers']);

    		foreach ($result as $lead) {
    			pr($lead);
    		}
    	}
    }

src/Model/Table/LeadsTable.php

<?php

namespace App\Model\Table;

use Cake\ORM\Table;
use Cake\Validation\Validator;

class LeadsTable extends Table
{
	public function initialize(array $config)
	{
		$this->belongsToMany('Openers', [
			'className' => 'Users',
			'foreignKey' => 'lead_id',
			'bindingKey' => 'user_id',
			'through' => 'LeadsUsers',
		])
			->setConditions(['LeadsUsers.user_type' => 'Opener'])
			->setProperty('openers');

		$this->belongsToMany('Closers', [
			'className' => 'Users',
			'foreignKey' => 'lead_id',
			'bindingKey' => 'user_id',
			'through' => 'LeadsUsers',
		])
			->setConditions(['LeadsUsers.user_type' => 'Closer'])
			->setProperty('closers');
	}
}

src/Model/Table/UsersTable.php

<?php

namespace App\Model\Table;

use Cake\ORM\Table;
use Cake\Validation\Validator;

class UsersTable extends Table
{
	public function initialize(array $config)
	{
		$this->belongsToMany('Leads', [
			'foreignKey' => 'user_id',
			'bindingKey' => 'lead_id',
		]);
	}
}

It’s a many-to-many relationship between Leads and Users. I have created leads_users table in the database, but I do not have the src/Model/Table/LeadsUsersTable.php file.

With the above setup, I get openers and closers arrays in the result, but those are blank.

Thank you.

Zuluru · September 29, 2020, 10:02pm

Reading the documentation, it seems to me that you should be able to skip all of foreignKey, bindingKey and targetForeignKey, as the defaults should suffice for you. In particular, I think that bindingKey should be just “id” here, so maybe that’s the source of the issue?

Also, in my setup, I use joinTable instead of through for cases where I don’t have a specific class defined for that table. I think that with the way you have it, it should create a default object for you, with the correct table name in it, but I’m not sure on that.

suhaz619 · September 29, 2020, 10:38pm

Thank a ton, mate. I was using the proper conventions, but no luck. But then you gave me a key point: bindingKey should be just “id”. I think there is a flaw in my database schema. I am trying to merge openers and closers tables. Ideally, those should be different tables.

For now, I have changed my schema to below:

CREATE TABLE `leads_users` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `lead_id` int(10) NOT NULL,
 `opener_id` int(10) NOT NULL,
 `closer_id` int(10) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

and my LeadsTable.php to below:

public function initialize(array $config)
	{
		$this->belongsToMany('Openers', [
			'className' => 'Users',
			'foreignKey' => 'lead_id',
		])
			->setProperty('openers');

		$this->belongsToMany('Closers', [
			'className' => 'Users',
			'foreignKey' => 'lead_id',
		])
			->setProperty('closers');
	}

I am not having any code in UsersTable.php. It’s working as expected, but database-wise the records are not cleanly arranged. Even though it’s working correctly, I think the best way is that I should drop the leads_users table and create leads_openers and leads_closers tables.

Thank you so much for you patience and help. Much appreciated.

thomasg · October 8, 2020, 9:35am

If there is one opener and closer that is… Normally you can do leads (opener_id, closer_id)

And then in Users table

$this->hasMany(‘OpenedLeads’, [
‘className’ => ‘Leads’,
‘foreignKey’ => ‘opener_id’,
]);

In LeadsTable

$this->belongsTo(‘Openers’, [
‘className’ => ‘Users’,
‘foreignKey’ => ‘opener_id’,
‘joinType’ => ‘INNER’,
]);

Topic		Replies	Views
Multiple associations to same table Need Help help	1	250	March 13, 2023
Help with associations Need Help help	2	244	March 29, 2023
Need a help for database related joining tables in model. Version Cakephp 3.0 Need Help	2	341	December 21, 2018
Table associations - Multiple relations to the same model in CakePHP 4.2.4 Need Help	2	455	October 19, 2021
Multiple relations to the same same table Need Help help	2	232	June 8, 2023

Cakephp 3.9 Multiple Associations with the Same Table

Releases

Documentation

Community

Cakephp 3.9 Multiple Associations with the Same Table

Related Topics

Releases

Documentation

Community