Laravel Eloquent - Query where secondValue if no results with initialValue

Question

I have a model named Document, Document has a status field where the possible values are, let's say dummy_data ['done', 'failed', 'sent', ....].

I want to get all documents where status='done' if they are present in DB OR all documents where status='sent' if first condition is false.

I have tried this query but I get documents with both statuses.

$result->documents()->where(function ($query) {
                $query->where('status', 'done')
                    ->orWhere('status', 'sent');
            })->get();

Answer 1

If there is no problem, you can split into two queries. The code will also be easier to read.

// getting documents with status done
$documentsQuery = $result->documents()->where('status','done');
 

// if there are no documents, add the other condition
if(!$documentsQuery->exists()){
    $documentsQuery = $documentsQuery->orWhere('status','sent');
}

dd($documentsQuery->get());

Answer 2

You can just use orWhereRaw and do raw query for OR which check if first where has no result.

$result->documents()->where('status', 'done')
    // OR clause which checks if first where has no result
    ->orWhereRaw("status = 'sent' AND NOT EXISTS (SELECT 1 FROM documents WHERE status = 'done')") 
    ->get(); 

it should have the same query as below where the OR has additional subquery for checking the existence of the first where

WHERE (
    status = 'done' 
    OR status = 'sent' AND NOT EXISTS (SELECT 1 FROM documents WHERE status = 'done')
);

Answer 3

You can query in two steps, first query documents with status=done. If the documents cannot be found, then query the documents with status=sent.

$documents = $result->documents()
                    ->where('status', 'done')
                    ->get();

if ($documents->count() === 0) {
    $documents = $result->documents()
                        ->where('status', 'sent')
                        ->get();
}

Alternatively, you can also complete it in the same query, but the performance of this query method is poor (not recommended).

$documents = $result->documents()
                    ->where(function ($query) {
                        $query->where('status', 'done')
                              ->orWhere(function ($query) {
                                  $query->where('status', 'sent')
                                        ->whereNotExists(function ($query) {
                                            $query->from('documents')
                                                  ->where('documents.status', 'done');
                                        });
                              });
                    })
                    ->get();