CODEWITHSUNDEEP
phplaraveleloquentpermissions
mps
mps

Reputation: 471

Laravel spatie permission many to through? query

I have two tables: Schedules and Tasks, with a one-to-one relation

class Schedule extends Model
{
    public function task() {
        return $this->belongsTo(Task::class, 'task_id');
    }

And the Task model has a one-to-many relation with Spatie Roles with a task_role pivot table:

class Task extends Model
{
    public function roles() {
        return $this->belongsToMany(Role::class);
    }

How can I make a query that retrieves all schedules associated with tasks with permission for the logged in user?

For example: Tasks:

id name
1 task1
2 task2
3 task3

task_role:

task_id role_id
1 1
2 3
3 1

Schedule:

id name task_id
1 schedule1 1
2 schedule2 1
3 schedule3 5

Spatie model_has_roles:

role_id model_type model_id
1 App\Models\User 2
2 App\Models\User 1
3 App\Models\User 5

When user2 is logged in he should be able to see only schedule 1 and 2.

Upvotes: 0

Views: 85

Answers (1)

Samuel Aramide
Samuel Aramide

Reputation: 601

It would have been ideal to use hasManyThrough but this would mean some modifications to your schema. However, you can achieve this result by chaining Eloquent relationships and a custom query. For your example, you can do this by

getting all the role IDs of the user then getting all tasks associated with these roles and finally fiter schedules based on this ID'.

Or you can define a custom method on user to do same.

example with code.


// In your Schedule model

public static function getSchedulesForUser($user)
{
   // Get the role IDs of the user
    $roleIds = $user->roles()->pluck('id');

    // Get tasks associated with these roles using Eloquent relationship
    $tasks = Task::whereHas('roles', function ($query) use ($roleIds) {
        $query->whereIn('id', $roleIds);
    })->get();

    // Extract task IDs from the tasks collection
    $taskIds = $tasks->pluck('id');

    // Return the schedules associated with these tasks
    return self::whereIn('task_id', $taskIds)->get();
}

or

//in your User model 


  public function schedules()
    {
        // Get role IDs
        $roleIds = $this->roles()->pluck('roles.id');

        // Get task IDs associated with these roles
        $taskIds = Task::whereHas('roles', function ($query) use ($roleIds) {
            $query->whereIn('roles.id', $roleIds);
        })->pluck('tasks.id');

        // Return schedules associated with these tasks
        return Schedule::whereIn('task_id', $taskIds)->get();
    }

or

//directly in your controller 

// Get the logged-in user
$user = Illuminate\Support\Facades\Auth::user();

// Get the role IDs of the logged-in user
$roleIds = $user->roles->pluck('id');

// Get the task IDs associated with these roles
$taskIds = DB::table('task_role')
    ->whereIn('role_id', $roleIds)
    ->pluck('task_id');

// Get the schedules associated with these tasks
$schedules = Schedule::whereIn('task_id', $taskIds)->get();

Upvotes: 2

Related Questions