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ı
– Uğur Arıcı, Commented Aug 9, 2021 at 15:46
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
– Fuksik64, Commented Aug 9, 2021 at 16:02

Uğur Arıcı · Accepted Answer · 2021-08-09 16:54:57Z

2

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);
}

edited Aug 9, 2021 at 16:54

answered Aug 9, 2021 at 16:09

Uğur Arıcı

1,2902 gold badges11 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Fuksik64 Over a year ago

Thats a lot better than mine, but isn't there any way to make it with single eloquent method?

Fuksik64 Over a year ago

$manager = Manager::with(['users'])->where('user_id', '=', auth()->user()->id)->first(); $vacations = Vacation::whereIn('user_id', $manager->users()->pluck('user_id'))->get();

Uğur Arıcı Over a year ago

I think you can use hasManyThrough for it, I will check and update my answer.

Collectives™ on Stack Overflow

Retrieve data from database filtered by pivot table Laravel

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related