Laravel spatie permission many to through? query

Question

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.

Answer 1

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