Need a help for database related joining tables in model. Version Cakephp 3.0


#1

SELECT admin.username,user_data.username FROM leaves,users as admin,users as user_data WHERE admin.id=admin_id and user_data.id=user_id

I want this type of join as shown above query.

We have created code, see below:-
class LeavesTable extends Table
{
public function initialize(array $config)
{
parent::initialize($config);

    $this->table('leaves');
    $this->displayField('id');
    $this->primaryKey('id');

$this->belongsTo(‘Users’, [
‘propertyName’=>‘admindata’,
‘foreignKey’ => ‘admin_id’,
]);
$this->belongsTo(‘Users’, [
‘propertyName’=>‘userdata’,
‘foreignKey’ => ‘user_id’,
]);

class UsersTable extends Table
{
public function initialize(array $config)
{
parent::initialize($config);

    $this->table('users');
    $this->displayField('username');
    $this->primaryKey('id');

     $this->hasMany('Leaves', [
        'foreignKey' => 'user_id'
    ]);


#2

Your code formatting is a mess here, but it looks like the problem is that you’ve tried to add two associations with the same name, Users. You need to give them unique association names, even if the underlying model that they’re referencing is the same. Something like this:

$this->belongsTo(‘Admins’, [
    ‘propertyName’=>‘admindata’,
    ‘foreignKey’ => ‘admin_id’,
]);

#3

$this->belongsTo(‘userdata’, [
‘propertyName’=>‘userdata’,
‘foreignKey’ => ‘user_id’,
‘joinType’ => ‘INNER’,
‘className’ => ‘Users’,
]);
$this->belongsTo(‘admindata’, [
‘propertyName’=>‘admindata’,
‘foreignKey’ => ‘admin_id’,
‘joinType’ => ‘INNER’,
‘className’ => ‘Users’,
]);