Laravel - Joining 3 tables in Eloquent

Question

I'm trying to join 3 tables in Eloquent namely users, departments, and roles. A user can only have one department and one role. Here's how I've defined the models:

user model:

    public function department(){
        return $this->has_one('Department', 'department_id');
    }

    public function role(){
        return $this->has_one('Role', 'role_id');
    }
}

department

<?php
class Department extends Eloquent 
{
    public static $key = 'department_id';
    public static $table = 'sys_departments';
    public static $timestamps = false;

    public function user(){
        return $this->belongs_to('User', 'user_id');
    }
}

role

class Role extends Eloquent 
{
    public static $key = 'role_id';
    public static $table = 'sys_roles';
    public static $timestamps = false;

    public function user(){
        return $this->belongs_to('User', 'user_id');
    }

    public function transaction(){
        return $this->has_many('Transaction', 'transaction_id');
    }
}

And here's how I'm accessing them in the controller:

$user = User::with(array('department', 'role'))->where('user_id', '=', 1)->first();
echo $user->department->department;

It's producing these queries:

SELECT * FROM `tbl_users` WHERE `user_id` = '1' LIMIT 1
SELECT * FROM `tbl_users` WHERE `user_id` = '1' LIMIT 1
SELECT * FROM `sys_departments` WHERE `department_id` IN ('1')
SELECT * FROM `sys_roles` WHERE `role_id` IN ('1')

Any ideas?

Eloquent doesn't use joins for a multitude of reasons, mainly they add complexity. If you want to do it using one query you will need to use fluent. — William Cahill-Manley, Nov 26 '12 at 12:09

score 4 · Accepted Answer · edited Oct 11 '13 at 11:08

I believe you are confusing the relationship methods, from the laravel docs

class User extends Eloquent {

 public function phone()
 {
      return $this->has_one('Phone', 'my_foreign_key');
 }
}

The identifier my_foreign_key is the name of the foreign key in the phones table and it references the user id(the owner of the relationship). so in your case

public function department(){
    return $this->has_one('Department', 'your_user_id');
}

where your_user_id is the field you use in your deparments table to reference the user it belongs to. and as well in the other examples you provided

currently you are setting the foreign key to reference the same table and not its owner.

Laravel - Joining 3 tables in Eloquent

1 Answers1