在laravel模型中处理关系

Question

I am learning relationships in Laravel php framework and I am trying to build this query

SELECT * FROM users u INNER JOIN link_to_stores lts ON u.id=lts.user_id INNER JOIN stores s ON lts.store_id=s.store_id WHERE lts.privilege = 'Owner'

I built this in Model

Link_to_store.php

public function store()
{
    return $this->belongsTo('App\Store');
}

public function user()
{
    return $this->belongsTo('App\User');
}

User.php

public function store_links()
{
    return $this->hasMany('App\Link_to_store');
}

Store.php

public function user_links()
{
    return $this->hasMany('App\Link_to_store');
}

I tried this query but this only joins user and link_to_store table

$personal_stores = Auth::user()->store_links->where('privilege','=','Owner');

Now I am confused how to join store table too. Can anyone help with this?

Schema is like this

Stores Table

store_id store_name

Users Table

id name

Link_to_stores Table

id store_id user_id privilege

Answer 1

I suppose store_links is actually a pivot table. In this case, you can use belongsToMany(), this will automatically take care of the pivot table.

To do this, in your User model you change the store function to this:

function stores() {
    return $this->belongsToMany('App\Store', 'store_links', 'user_id', 'store_id')->withPivot('privilege');
}

Because the primary key of stores is not id, you will have to define this in you Store model with the following line:

protected $primaryKey = 'store_id';

Now to get the stores for a user, you simply call

$stores = Auth::user->stores()->wherePivot('privilege', 'Owner')->get();