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();

You'll need to separate it out into 2 different queries, I believe. Check for documents that are done. Then if there are no results, run another query for sent — aynber
– aynber, Commented Jan 17, 2024 at 19:05

Kosher · Accepted Answer · 2024-01-18 04:00:25Z

1

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')
);

answered Jan 18, 2024 at 4:00

Kosher

5,4161 gold badge21 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

miken32 Over a year ago

I would put that as separate where() and whereSub() statements inside a where() closure to keep raw use to a minimum and get the explicit parentheses in the query but +1 this is the correct approach

Lucas Pace · Accepted Answer · 2024-01-17 19:25:16Z

0

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());

answered Jan 17, 2024 at 19:25

Lucas Pace

2771 silver badge15 bronze badges

Comments

yasiao · Accepted Answer · 2024-01-18 08:31:52Z

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();

Collectives™ on Stack Overflow

Laravel Eloquent - Query where secondValue if no results with initialValue

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related