Eloquent: Constraining an eager load relation where relation property is 0 or relation doesn't exist

Question

I would like to get all the Report models where the relation ReportUpload's property of status equals 0 or where the ReportUpload relation doesn't exist. The Report and ReportUpload models have a one to one relationship, ReportUpload belongs to a Report.

Somewhat unsure how to go about this using eloquent's relationship constraints or any other method. Any help would be appreciated.

Here's my current code:

// initial query

$reports = Report::whereHas('link', function($query) {
        $query->where('status', 'complete');
    })->with('student', 'course', 'institution', 'reportUpload');


// apply constraint

if ($request->has('uploadStatus')) {
    $uploadStatus = $request->has('uploadStatus'); // 0 or 1

    if ($uploadStatus === 0) {
        $reports = $reports
            ->whereDoesntHave('reportUpload')
            ->orWhereHas('reportUpload', function($query) use ($uploadStatus) {
                $query->where('status', $uploadStatus);
            });
    } else {
        $reports = $reports->whereHas('reportUpload', function($query) use ($uploadStatus) {
            $query->where('status', $uploadStatus);
        });
    }
}

The code does not produce the desired results.

Edit

Trying this approach but not sure if it's correct:

$reports = $reports
    ->where(function ($query) use ($uploadStatus) {
        $query
            ->whereDoesntHave('reportUpload')
            ->orWhereHas('reportUpload', function($query) use ($uploadStatus) {
                $query->where('status', $uploadStatus);
            });
    });

Answer 1

First, there are some mistakes in your initial code.

1 - You're checking if the request has an uploadStatus. Then, $uploadStatus == $request->has which will always be true.

if ($request->has('uploadStatus')) {
    $uploadStatus = $request->has('uploadStatus');

So I guess you might want:

if ($request->has('uploadStatus')) {
    $uploadStatus = $request->input('uploadStatus');

2 - You're comparing strictly $uploadStatus === 0 which might not work because the request might return a string '0' and not an integer, so you should either compare with == or cast $uploadStatus to (int).

After this, I think the code you added in your question works as expected:

$reports = $reports
->where(function ($query) use ($uploadStatus) {
    $query
        ->whereDoesntHave('reportUpload')
        ->orWhereHas('reportUpload', function($query) use ($uploadStatus) {
            $query->where('status', $uploadStatus);
        });
});

Because the where encapsulating the query will put it between parentheses.

Answer 2

Try to separate the queries. whereDoesntHave might be counting negatively with the orWhereHas even if it is an or statement:

$reportsNoUpload = $reports
            ->whereDoesntHave('reportUpload')->get();

$reportsIncomplete = $reports
            ->orWhereHas('reportUpload', function($query) use ($uploadStatus) {
                $query->where('status', $uploadStatus);
            })->get();

$reports = $reportsNoUpload->merge($reportsIncomplete);