On a 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",
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);
}
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
}