Level 53
@Mirdrack Format your code bc it's impossible to read it. You can use whereHas or joins to achieve what you want, but that depends on your goal, so tell us what you tried.
Mar 26, 2015
4
Level 3
Query to filter by table field and privot table field
Hi!
Im developing a task system and I have 3 tables users, tasks and tasks_users
Here is the schema for the tables:
//tasks table
id | title | description | starts_at | ends_a t| published | created_at | updated_at | deleted_at
//users table
id| email | password | first_name | last_name | created_at | updated_at | deleted_at
//tasks_users table
id | task_id | user_id
The thing is I need to get the tasks partipants of unfinished tasks but i dont know how make a query to filter by one field on my tasks table and another field of my pivot table.
I hope somebody can help me, Thnks in advance
Level 3
Sorry about the code of Table schema I hope the edition helps .
For example i need to list tasks where task.completed = 0 and tasks_users.user_id = $someIdVariable in oder to show to the user all the unfished tasks where he is involve
I try with:
$tasks = $this->task->where('completed', '=', 0)
->wherePivot('user_id', '=', $id)
->paginate($this->paginationNumber);
But I dont know why eloquent was finding "privot" colum and taking "user_id" as parameter so i desist of that, I dont know if i should make a raw query but I think must be another way using eloquents methods
Level 53
@Mirdrack If all you need is the pivot table, then use simple join:
$this->task->where('completed', '=', 0)
->join('tasks_users as tu', function ($join) use($id) {
$join->on('tu.task_id', '=', 'tasks.id')
->where('tu.user_id', '=', $id); // this could go outside the join closure as well, since it's inner join
})
->select('tasks.*')
->paginate($this->paginationNumber);
Or you could also use Eloquent:
$this->task->where('completed', '=', 0)
->whereHas('users', function ($q) use ($id) {
$q->where('users.id', $id);
})
->paginate($this->paginationNumber);
The difference will be that 1st piece joins 1 table, and 2nd piece joins 2 tables, but it's abstracted thanks to Eloquent.
wherePivot works only in the context of a relation, ie.:
public function someRelation()
{
return $this->belongsToMany(..)->wherePivot(...);
}
// or
$model->someRelation()->wherePivot(...);
1 like
Level 3
Im gonna improve my knowledge of clousures, still a little bit tricky for me Thank you very much :D
Please or to participate in this conversation.