CODEWITHSUNDEEP
phpcakephpjoincakephp-3.0has-and-belongs-to-many
Valentin Rapp
Valentin Rapp

Reputation: 472

Self linking model in cakePHP 3 and retrieving data from the join table

im creating a WebApp with cakePHP 3.0.

I have a User Model and Table which has and belongs to many Users.

So i set up tables according to cake conventions: 

CREATE TABLE IF NOT EXISTS `users`
(
user_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
);

and the JOIN table:

CREATE TABLE IF NOT EXISTS `users_users`
(
supervisor_id int,
employee_id int,
FOREIGN KEY (supervisor_id) REFERENCES users(user_id),
FOREIGN KEY (employee_id) REFERENCES users(user_id)
);

In my UserTable.php i created these associations:

    $this->table('users');
    $this->primaryKey('user_id');

    $this->belongsTo('Users', [
                'foreignKey' => 'user_id',
                'joinType' => 'INNER'
   ]);

    $this->belongsToMany('Supervisors', [
                'className' => 'Users',
                'targetForeignKey' => 'supervisor_id'
    ]);
    $this->belongsToMany('Employees', [
                'className' => 'Users',
                'targetForeignKey' => 'employee_id'
    ]);

Now in a new model method i want to get the Supervisors of one employee: 

public function getSupervisorEmail($user_id) {
        $supervisors = $this->Employees->Supervisors->find()->select(['user_id']);
        debug($supervisors);
        return $supervisors;
    }

I put sample data in my users_users Table but i dont know how i can access these entries. The query in the function above doesnt do what i expect it to do. It just returns a record from my Users Table without joining with users_users Table what i dont understand because i set all up according to naming conventions of cakePHP 3...

How can i access my join Table and get associated records? EG get Supervisors associated to user with user_id 1. I tried different querys but none used my join table.

Thanks for answers!

Upvotes: 1

Views: 2837

Answers (3)

floriank
floriank

Reputation: 25698

Specify the join table object in the through option of the HABTM (In 3.0 it has been renamed to belongsToMany / BTM) assoc. Otherwise it will inflect and construct a Table object instance based on the table name. I would always create the join table object, so just bake it for now.

http://book.cakephp.org/3.0/en/orm/associations.html#belongstomany-associations

through Allows you to provide a either the name of the Table instance you want used on the join table, or the instance itself. This makes customizing the join table keys possible, and allows you to customize the behavior of the pivot table.

You're not querying additional assocs. contain() your join table model in your query.

http://book.cakephp.org/3.0/en/orm/retrieving-data-and-resultsets.html#eager-loading-associations

By default CakePHP does not load any associated data when using find(). You need to ‘contain’ or eager-load each association you want loaded in your results.

$query = $articles->find('all');
$query->contain(['Authors', 'Comments']);

Upvotes: 2

manish kumar
manish kumar

Reputation: 11

$users = $this->Users->find('all', [
            'order' => [],            
            'conditions' => ['Users.id' => 1],
            'contain' => ['Supervisors','Employees']
                ]
        );

use this

Upvotes: 0

Valentin Rapp
Valentin Rapp

Reputation: 472

Problem was that I got the concept wrong. The SQL statement when you debug the query doesnt show every SQL stuff which happens internally in cakePHP so u dont see the automatic joins.

But now i figured out that it works perfectly by echoing testdata like in the cakePHP book http://book.cakephp.org/3.0/en/orm/associations.html#belongstomany-associations

Thanks though @burzum for trying to help me the provided links are always helpfull.

Upvotes: 1

Related Questions