Retrieve data from database filtered by pivot table Laravel

Question

I've got table structure like this:

users:

id
name

managers:

id
user_id

user_manager:

manager_id
user_id

vacations:

user_id
date_from...

My task is to fetch all data from vacations table where user (vacations.user_id) belongs to given manager id (managers.user_id) and that assigment is in pivot table user_manager. User can have many managers, manager can have many users. I've been trying to do this, but I've got stuck. My workaround was this:

        DB::table("vacations")
        ->leftJoin("users", function ($join) {
            $join->on("vacations.user_id", "=", "users.id");
        })
        ->leftJoin("user_manager", function ($join) {
            $join->on("user_manager.user_id", "=", "users.id");
        })
        ->where("user_manager.manager_id", "=", $id)
        ->get();

But I want to know a way to make this with eloquent model and all of it's fancy stuff.

Manager model:

class Manager extends Model
{
    use HasFactory;
    protected $table = 'managers';
    protected $fillable = ['user_id'];

    public function users()
    {
        return $this->belongsToMany(User::class, 'employee_manager')->withTimestamps();
    }
}

User model:

class User extends Model
{
    use HasFactory, Notifiable;

    public function managers()
    {
        return $this->belongsToMany(Manager::class, 'employee_manager')->withTimestamps();
    }
}

Is your User model's class name also Manager ? And to make it clear; you are trying to fetch an exact manager's users who has a vacation plan, did I understand it right? — Uğur Arıcı
My bad, just updated it in question. No my User class is named "User". I'm trying to fetch all Vacations assigned to User that is assigned to Manager (one user can have many managers). It's feature to accept user's request for vacations that are under You — Fuksik64

Uğur Arıcı Uğur Arıcı · Accepted Answer · 2021-08-09T16:09:47

You are trying to get all Vacation records which created by a User which belongs to a specific Manager

It means you need to get all Vacations where user_id in that Manager 's users()

You can achieve this with a basic way like this;

$vacationsThatConcernTheManager = Vacation::whereIn('user_id', $manager->users()->pluck('id'))->get();

And if you want use directly something like $manager->vacations() you can use hasManyThrough as described in documentation.

Add this relation to your Manager model;

public function vacations()
{
    return $this->hasManyThrough(Vacation::class, User::class);
}

Retrieve data from database filtered by pivot table Laravel

1 Answers