CODEWITHSUNDEEP
phplaraveleloquent
Fuksik64
Fuksik64

Reputation: 5

Retrieve data from database filtered by pivot table Laravel

I've got table structure like this:

users:

managers:

user_manager:

vacations:

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

Upvotes: 0

Views: 62

Answers (1)

Uğur Arıcı
Uğur Arıcı

Reputation: 1280

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

Upvotes: 2

Related Questions