Laravel eloquent join with multiple wheres

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 😀

>Solution :

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.
}