Join where any of the relation passes condition

I have a query something like

    $projects = Project::with('tasks')
        ->select('projects.*')
        ->join('tasks', 'projects.id', '=', 'tasks.project_id')

I need to join only if the task has a certain status e.g. done. Currently I am trying

    $projects = Project::with('tasks', 'company')
        ->select('projects.*')
        ->join('tasks', 'projects.id', '=', 'tasks.project_id')
        ->where(function (Builder $query) use ($projectsListRequest){
            if (empty($projectsListRequest->taskStatus))
                return;
            $query->where('tasks.status', $projectsListRequest->taskStatus);
        })

But it adds tasks with different status too. Any idea how to solve this? I guess the join needs another condition.

I guess no matter what I do the tasks will be eager loaded because the tasks method on the model does not have the filter. But this is just a guess.

Solution

I can see you are using a tasks relationship. You can use this relationship to return only the projects that has some task using the whereHas function. The whereHas allow to alter the query in a manner that will limit the results to the model with one or more related models, Projects with Tasks, in this case.

This function needs at least one parameter, which is the relation, and an optional closure that can be used to alter to lookup query of the relation.

You can use it like that:

 $projects = Project::with('tasks')
        // ->select('projects.*') since you are no longer using a join, you don't need the select.
        ->whereHas('tasks')

Now, you mentionned that you need to "join only if the task are done". This can be achieve using the Closure argument, in which we can specify some wheres.

 $projects = Project::with('tasks')
              ->whereHas('tasks', function($query) use ($projectsListRequest){
                  $query->where('status', $projectsListRequest->taskStatus)
              });

This will returns only the projects with task that have the status property set to whatever is in $projectsListRequest->taskStatus.

Also, cince you are using a if in your closure, you could use a when, before the whereHas, to not restrict the project if there's no status in $projectsListRequest->taskStatus.

 $projects = Project::with('tasks')
              // only apply the following "whereHas" if the condition is true
              ->when(!empty($projectsListRequest->taskStatus), function($query) use ($projectsListRequest) {
                  $query->whereHas('tasks', function($query) use ($projectsListRequest){
                      $query->where('status', $projectsListRequest->taskStatus)
                  });
              });

this query would look something like this, when the condition is true

select * FROM projects where exists ( select * from tasks where project_id = projects.id)

which does the same as a join but is way more efficient.

EDIT

Turns out the issue was with the eager loading. OP only wanted to eager load the task with a certain status.

This can be achieve by passing another parameter to the with function , which take the query builder that will be used to fetch the eagerloaded values. Using this query builder, we can alter the query to return only the tasks with a given status

 $projects = Project::with('tasks', function($query) {
                 $query->where('status', ...);
             })
             /* ... */