WLFCRK WLFCRK - 1 month ago 8
MySQL Question

Laravel 5 nested relation with condition

I've these tables:

Users

|id|role_id|clan_id|

Roles

|id|slug|

Clans

|id|

I need to grab all Users where slug is for example 'leader'

How can I do it?

What I got so far:

class clan extends Model{
public function leader()
{
$leader = User::whereHas('role', function($query) {
$query->where('slug', 'leader');
})->where('clan_id', $this->id)->get();

return $leader;
}
}


But this wouldn't be smart. Instead of this I would like to have it joined my clans table

Clans:

|id|leader_user_id|

so I can access it easily.

Thanks alot :)

Answer

You can create a one-to-many relations between clan and user as:

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

And in your leader() function you can write as:

public function leader() 
{
    $leader = $this->users()->whereHas('role', function($query) {
        $query->where('slug', 'leader');
    })->get();

    return $leader;
}

Update

To the response of comment below

You can create a scope as:

public function scopeLeader($query)
{
    return $query->with(['users' => function($query) {
               $query->whereHas('role', function($q) {
                    $q->where('slug', 'leader');
                });
}

And You can fetch Clan as:

Clan::where('name', $clanname)->leader()->get()
Comments