laravel eloquent: whereDate not found record included into the period

Question

I have a table with two date fields:

• reservation_begin
• booking_end

I want to know the reservations that occupy me from August 25th to August 31st.

In the select query I have:

Booking::select ('id', ''name', reservation_begin', 'reservation_end')
                        ->whereDate('reservation_begin', '>=', $startDate)
                        ->whereDate('reservation_end', '<=', $endDate)
                        ->get();

I will NOT find the record in the database if: $startDate = '2021-08-25'; $endDate = '2021-09-01' (from 25 August to 1 September).

I want to have reservations that include the period from August 25 to August 31, and the reservation that goes from August 25 to September 1 still includes that period, how can I get it out?

Thank you for your help

Answer 1

Here's a test case with direct SQL, just to show the various logic cases and results. This can help determine the laravel/eloquent logic to use:

Working SQL test case (updated)

A table with some data (using MySQL):

CREATE TABLE booking (
    booking_id         int   primary key auto_increment
  , reservation_begin  date
  , reservation_end    date
);

INSERT INTO booking (reservation_begin, reservation_end) VALUES
    (current_date, current_date + 3)
  , ('2021-08-25', '2021-09-01')
  , ('2021-08-21', '2021-08-24')
  , ('2021-08-23', '2021-08-25')
  , ('2021-09-01', '2021-09-04')
  , ('2021-09-02', '2021-09-07')
;

Your logic and result:

WITH args (xstart, xend) AS (SELECT '2021-08-25', '2021-09-01')
SELECT t1.*, args.*
  FROM booking AS t1
  JOIN args
    ON t1.reservation_end   <= args.xend 
   AND t1.reservation_begin >= args.xstart 
;

Include overlap cases (return just the matches) and result:

WITH args (xstart, xend) AS (SELECT '2021-08-25', '2021-09-01')
SELECT t1.*, args.*
  FROM booking AS t1
  JOIN args
    ON args.xstart <= t1.reservation_end
   AND args.xend   >= t1.reservation_begin
;

Include overlap cases (return the matches, plus null for the non-matches), plus results:

WITH args (xstart, xend) AS (SELECT '2021-08-25', '2021-09-01')
SELECT t1.*, args.*
  FROM      booking AS t1
  LEFT JOIN args
    ON args.xstart <= t1.reservation_end
   AND args.xend   >= t1.reservation_begin
;