Get records from two date columns in db in laravel via eloquent

Question

i have a leaves table called user_leaves in which leaves taken by users are stored, table has this structure.

id, leave_type_id, from_date, to_date, status, created_at, updated_at.

leave_type_id is basically a foreign key of types of leaves my system has.

I want to fetch all leaves taken by a user in a range of two dates like start_date and end_date (for example all leaves from 2020-08-01 to 2020-08-31 .

I want records in numbers like,

 {
   "user_id" : 1,
   "leaves" : 
    {
    "medical_leave" : 2,
    "earned_leave" : 5,
    "sick_leave" : 3
    }
  }

I used the code

                   $UserLeaves = UserLeave::whereDate('date_from', '>=', $start_date)
                            ->whereDate('date_from', '<=', $end_date)
                            ->get();

The problem is that if i choose from_date to query my result ,and then loop through $UserLeaves to get all days between from_date and to_date for each leave_type. Their are cases when to_date may exceed my end_date and thus giving wrong data if i calculate all days between from_date and to_date.

Dev Man · Accepted Answer · 2020-08-25 16:17:42Z

1

You would do something like this

$baseQuery = $baseQuery->where(function ($query) use ($date_created_from, $date_created_to) {
    if (isset($date_created_from) && $date_created_from != '') {
        $dateFrom = Carbon::parse($date_created_from)->format('Y-m-d');
        $query->whereDate('date_column', '>=', $dateFrom);
    }

    if (isset($date_created_to) && $date_created_to != '') {
        $dateEnd = Carbon::parse($date_created_to)->format('Y-m-d');
        $query->whereDate('date_column', '<=', $dateEnd); 
    } 
});

Where

$baseQuery is the query you write to perform all your selections, aggregation etc.

The above code accounts for null values and additionally you could perform additional checks before appending the query to your base query like "date from is not greater than 1 week before today".

answered Aug 25, 2020 at 16:17

Dev Man

2,1473 gold badges24 silver badges40 bronze badges

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

Comments

Naveed Ali · Accepted Answer · 2020-08-25 16:16:30Z

0

you can use whereBetween clause and in wherebetween you can pass $from_date and $to_date as below:

$from = date('2018-01-01');
$to = date('2018-05-02');

youModal::whereBetween('column_from', [$from, $to])->get();

answered Aug 25, 2020 at 16:16

Naveed Ali

1,0458 silver badges16 bronze badges

Collectives™ on Stack Overflow

Get records from two date columns in db in laravel via eloquent

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related