Laravel 8 query builder filter results by two datetime columns with whereDate

Question

I'm using Laravel 8's query builder to return a particular result from my database. I've got my query partially working with most of what I need but am having some issues trying to filter based on two particular datetime columns.

I've got two columns: period_from and period_to, I'd like to be able to return the relevant results after a given date on the period_from column, but before the period_to column with a different date, this is what I've got so far:

$events = GoogleAnalytics::where('event_category', $category)
                        ->where('event_action', $action)
                        ->whereDate('period_from', $dateFrom)
                        ->whereDate('period_to', $dateTo)
                        ->orderBy('created_at', 'desc')
                        ->first();

This unfortunately doesn't work, I can get results if I drop the period_to column, but I need to filter between the two dates. Why isn't this working? Can I have some guidance please? :)

I think you need something whereBetween('period_from ', [$dateFrom, $dateTo]) — sta
Use where. As in ->where('period_from', '>=', $dateFrom)->where('period_to', '<=', $dateTo) — user3532758
Neither of the above suggestions work for me. whereBetween returns no results, and using the combination of where returns the results for just a single day. I need to basically get everything after period_from from a given date, to everything up until period_to but after period_from — Ryan H
You have first() in your query, maybe that's the reason for single day? Change to get() and check — user3532758
I've got ->first() because I only need the latest result from the returned results — Ryan H

Kenath Kenath · Accepted Answer · 2021-01-13T11:23:21

Try to add the operators along with the where query.

$events = GoogleAnalytics::where('event_category', $category)
                    ->where('event_action', $action)
                    ->where('period_from', '<', $dateFrom)
                    ->where('period_to', '>', $dateTo)
                    ->orderBy('created_at', 'desc')
                    ->first();

Laravel 8 query builder filter results by two datetime columns with whereDate

1 Answers