How to do conditional query in Laravel

Question

The situation is following. The table has 2 date fields and last date is nullable.

In this table I want to get the 1st and 2nd records when I pass 2020-02-10 as parameter. That is between start_end and end_date or bigger than start_date if end_date is null.

How to do Laravel eloquent for this. Thank you in advance.

Clément Baconnier · Accepted Answer · 2020-02-19 19:36:06Z

1

It's kind of ugly, but I think something like that should work

Model::where(function($query) use ($date){
    $query->where('start_date', '<=', $date)
        ->where('end_date', '>=',  $date);
})->orWhere(function($query) use ($date) {
    $query->where('start_date', '<=', $date)
        ->whereNull('end_date');
})->get()

Edit Since you need other condition with it, you need to wrap this condition

Model::query()
->where(function($query) use ($date) {
    $query->where(function($query) use ($date){
        $query->where('start_date', '<=', $date)
            ->where('end_date', '>=',  $date);
    })->orWhere(function($query) use ($date) {
        $query->where('start_date', '<=', $date)
            ->whereNull('end_date');
    })
})
->where('another_column', true)
->get()
```

edited Feb 19, 2020 at 19:36

answered Feb 19, 2020 at 18:32

Clément Baconnier

6,2126 gold badges35 silver badges63 bronze badges

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

2 Comments

Jihun Over a year ago

Thank you for quick answer. If I have another fields like is_deleted and I should check the field how to implement multiple condition?

Clément Baconnier Over a year ago

You need to wrap the where and the orWhere in an another where(function($query){....} (three level deep) See the anonymous function as parenthesis.

Collectives™ on Stack Overflow

How to do conditional query in Laravel

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related