I have 2 Models:
- Project
- Task
A Project has multiple tasks and a task has only 1 project. a task also has a start week, start year, end week and end year, what i want is
Select all the projects and join the tasks where task startWeek = $startWeek and startYear = $startYear and endWeek = $endWeek and endYear = $endYear
So i want to get all the projects and join the tasks that start and end between these weeks and years.
I already tried a few things, one of them being:
$projects = Project::join('tasks', 'tasks.project_id', '=', 'projects.id')
->where('tasks.start_week', '>=', $startWeek)
->where('tasks.start_week', '>=', $startWeek)
->where('tasks.end_week', '<=', $endWeek)
->where('tasks.end_year', '<=', $endYear)
->get();
but that returns
0 : {
id:1
name:Schmeler
location:Harvey
created_at:2022-04-26T21:47:55.000000Z
updated_at:2022-04-26T21:47:55.000000Z
project_id:3
task_name:O'Hara
start_week:41
start_year:2022
end_week:5
end_year:2023
}
But i want the task to be in an array like
id: 1,
name: Schmeler,
...other items
tasks: {
0: {
task_id: 1,
task_name: Task2,
},
1: {
task_id: 2,
task_name: Task3
}
}
Any help is welcome :D
CodePudding user response:
You should not use joins for this, instead use relationships, as you get the expected structure by default.
class Project
{
public function tasks()
{
return $this->hasMany(Task::class);
}
}
Now you can load your tasks with these conditions, to filter relationships, the easiest approach is to include them using with()
and query em from there.
Project::with(['tasks' => function ($query) use ($startWeek, $startYear, $endWeek, $endYear) {
$query->where('tasks.start_week', '>=', $startWeek)
->where('tasks.start_week', '>=', $startYear)
->where('tasks.end_week', '<=', $endWeek)
->where('tasks.end_year', '<=', $endYear);
}])->get();
Your data will be in your wanted structure, for API usage you can just return the project and it will automatically transform it.
{
$projects = Project::with(...)->get();
return $projects;
}
For iterating or more traditional blade approaches you would be able to loop it like this.
foreach($project->tasks as $task)
{
$task->task_name; // etc.
}