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
>Solution :
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