Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel – How in user model with pivot to tasks to restrict to only uncompleted tasks?

On laravel 10 site I have 3 related tables :

Schema::create('users', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    ...
    $table->timestamp('created_at')->useCurrent();
});


Schema::create('tasks', function (Blueprint $table) {
    $table->id();
    $table->foreignId('creator_id')->references('id')->on('users')->onUpdate('RESTRICT')->onDelete('CASCADE');
    $table->boolean('completed')->default(false);
    ...
    $table->timestamp('created_at')->useCurrent();
    $table->timestamp('updated_at')->nullable();
});


Schema::create('task_user', function (Blueprint $table) {
    $table->id();
    $table->unsignedBigInteger('user_id')->unsigned()->index();
    $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');

    $table->foreignId('task_id')->references('id')->on('tasks')->onUpdate('RESTRICT')->onDelete('CASCADE');
    $table->timestamp('created_at')->useCurrent();
    $table->unique(['user_id', 'task_id']);
});

I defined pivot relation in app/Models/User.php :

public function tasks(): belongsToMany {
        return $this->belongsToMany(Task::class);
}

But how can I in this relation to restrict to only uncompleted tasks, where

tasks.completed = false

?

"laravel/framework": "^10.48.7",

Thanks in advance!

Error Notice :

I tried as @Ignacio Amores proposed, but got error :

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'task_user.completed' in 'where clause'

select
  `tasks`.*,
  `task_user`.`user_id` as `pivot_user_id`,
  `task_user`.`task_id` as `pivot_task_id`
from
  `tasks`
  inner join `task_user` on `tasks`.`id` = `task_user`.`task_id`
where
  `task_user`.`user_id` = 1
  and `task_user`.`completed` = 0

as I need to apply filter on tasks.completed, but not task_user.completed, as it is rendered using wherePivot method …

I got data with code :

$this->profileUser->uncompletedTasks

and added method in User model :

public function uncompletedTasks(): belongsToMany {
    return $this->belongsToMany(Task::class)->wherePivot('completed',false);
}
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Decision is :

    public function uncompletedTasks(): belongsToMany {
    return $this->belongsToMany(Task::class)
        ->using(TaskUser::class)
        ->withPivot('supervisor_id')   // Reference to "task_user" table
        ->where('completed',false);  // Reference to "tasks" table
}

  2. 0

    I´m very new to Laravel, but in the Laravel´s documentation apperas to be the wherePivot() method for cases like this:

    public function tasks(): belongsToMany {
        return $this->belongsToMany(Task::class)->wherePivot('completed',false);
}

    https://laravel.com/docs/10.x/eloquent-relationships#filtering-queries-via-intermediate-table-columns
    As I understand it, this could resolve the problem.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search