Laravel Eloquent Join 2 Tables

Question

These are my models:

class Branch extends Eloquent {

    protected $table = 'tb_branch';

    public function faculty() 
    {
        return $this->hasMany('Faculty');
    }
}

class Faculty extends Eloquent {

    protected $table = 'tb_faculty';

    public function branch() 
    {
        return $this->belongsTo('Branch');
    }

}

In branch table I have set a column with foreign key user_id that links to users table.

In faculty table I have set a column with foreign key branch which links to branch table with column name.

And now I need to retrieve Faculty data based on currently logged in user (with Eloquent).

I imagine the work flow would be:

retrieve id of currently logged in user
link that id to the branch's user_id and retrieve branch's name
link that branch's name to faculty branch's name and retrieve all matched faculty data

Now this is what I have tried, it didn't work:

$user_id = Sentry::getUser();
$faculty = Faculty::with('branch')->where('branch.user_id',$user->id)->get();

Error says:

Unknown column 'branch.user_id' in 'where clause' (SQL: select * from tb_faculty where branch.user_id = 6)

How can I achieve this with Eloquent?

Rustam Rustam · Accepted Answer · 2014-05-20T10:13:15

retrieve id of currently logged in user
link that id to the branch's user_id and retrieve branch's name
link that branch's name to faculty branch's name and retrieve all matched faculty data

so, first way:

$user_id = Auth::user()->id;
$branch = Branch::where('user_id', '=', $user_id)->first();
$faculties = Faculty::where('branch_name', '=', $branch->name)->get();

second way:

if faculties are based on name, then:

class Branch extends Eloquent {

   public function faculties() {

    return Faculty::where('branch_name', '=', $this->name)->get();
    /*

     or do this: return $this->hasMany('Faculty', 'branch_name', 'name');       

    */

   }


}

Then do this:

$user_id = Auth::user()->id;
$faculties = Branch::where('user_id', '=', $user_id)->first()->faculties;

then in the view:

foreach($faculties as $fac)
{

  echo $fac->name.'<br />';
}

Laravel Eloquent Join 2 Tables

1 Answers