Eloquent whereBetween query is being ignored-CodePudding

I need to get a Users' Booking between two dates. But when I run the Eloquent query, it brings back bookings that are not between the two given dates. I think it might be down to how the query is ran as priority? However thought I'd ask.

So for context

User HasMany Bookings
Bookings (Date) is a date column in my database
Date range is from 07-07-2022 to 07-21-2022

The ran query for my database is :

select * from `users` 
where exists 
(select * from `bookings` 
where `users`.`id` = `bookings`.`user_id` 
and DATE(bookings.date) 
between '2022-07-07' 
and '2022-07-21' 
and `bookings`.`deleted_at` is null order by `date` asc) 
and `users`.`deleted_at` is null

My Eloquent is as follows :

$users = User::whereHas('bookings', function($q) use ($start, $end)
        {
            $q->whereBetween(\DB::raw('DATE(bookings.date)'), [$start, $end]);
        })->get();

This is a debug of the result of this code, As you can see there's some dates outside of my needed range.

I am using Laravel 5.8

CodePudding user response：

Let's get into the difference between whereHas and with

Where

This method allows you to query based on relationship existence. This is like saying.

pseudo code

SELECT * FROM user WHERE HAS BOOKS THAT HAVE DATE BETWEEN ? and ?

with

This method gives you the ability to load in data from a relationship and gives you the ability to constrain the received data. see >> Constraining Eager Loads

pseudo code

SELECT * from users AND also select * from books where date between ? and ?

In your case, you can combine them and do something like:

User::whereHas('books', function ($query) {
    $query->whereDateBetween('date', [$from, $till]);
})->with(['books' => function ($query) {
    $query->whereDateBetween('date', [$from, $till]);
}])

^^ This gives you only users who have books between those dates and gives you a collection instance of books on the user with the correct data

CodePudding user response：

I'm using Laravel last version but when see the documentation of version 5.8, can't find whereBetween then use simple where in your query, may work:

$users = User::whereHas('bookings', function($q) use ($start, $end) {
        $q->where('bookings.date', '>=', $start)->where('bookings.date', '<=',  $end);
})->get();

$users = User::whereHas('bookings', function($q) use ($start, $end) {
        $q->where(\DB::raw('DATE(bookings.date)'), '>=', $start)->where(\DB::raw('DATE(bookings.date)'), '<=', $end);
})->get();