How to filter data between two dates using eloquent laravel?

Question

I want to filter data between two dates in laravel. I have table with column date where date is stored in Y-m-d format.

$total = App\Models\Data::where('age_id',$age->id)
            ->where('nationality',$nationality)
            ->when(isset($to), function($q){
                $q->whereBetween('dateTime',[$from,$to]);
            })->when(!isset($to), function($q){
                $q->where('dateTime',$from);
            })
            ->sum('count');

My condition is that $to sometimes can be null. But whereBetween requires both $from and $to for it to work. And whereIn fetch data of $from and $to but not between $from and $to. If only $from is filled I want to filter data on that requested date. And if $from and $to is filled I want to filter data between $from and $to and perform count on those data. How can I achieve that?

STA · Accepted Answer · 2021-02-03 16:19:16Z

3

Try this :

$data = App\Models\Data::where('age_id',$age->id)
            ->where('nationality', $nationality)
            ->when(isset($to), function($q) use($from, $to){
                $q->whereBetween('date', [$from, $to]);
            })
           ->when(!isset($to), function($q) use($from){
                $q->whereDate('date', $from);
            })
          ->get();

answered Feb 3, 2021 at 16:19

STA

35.3k9 gold badges49 silver badges62 bronze badges

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

1 Comment

sd077 Over a year ago

instead of get can I use sum because i am summing here

Jonnathan Carrasco · Accepted Answer · 2021-02-03 15:50:44Z

1

For this you can assign a value for $to when is null, for example:

if (!$to) {
    $to = now();
}

$data = Data::whereBetween('date',[$from, $to])->get();

answered Feb 3, 2021 at 15:50

Jonnathan Carrasco

1661 silver badge9 bronze badges

Comments

N Mahurin · Accepted Answer · 2021-02-03 15:52:22Z

1

Eloquent comes with a when() function that can choose when parts of the query need to be executed.

$data = Data::when(isset($to), function($q){
            $q->whereBetween('date', [$from, $to]);
        })->when(!isset($to), function($q){
            $q->whereDate('date', $from);
        })->get();

answered Feb 3, 2021 at 15:52

N Mahurin

1,45611 silver badges18 bronze badges

4 Comments

sd077 Over a year ago

I am getting undefined variable $from now

sd077 Over a year ago

I have edited my function, can you please see that

Hamza Qureshi Over a year ago

use this if($request->from){ $dates=$this->dateFilter($request->from,$request->to); $from=$dates[0]; $to=$dates[1]; }

sd077 Over a year ago

where should I use that

Collectives™ on Stack Overflow

How to filter data between two dates using eloquent laravel?

3 Answers 3

1 Comment

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related