0

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? :)

Ryan H
  • 2,620
  • 4
  • 37
  • 109
  • 1
    I think you need something `whereBetween('period_from ', [$dateFrom, $dateTo])` – STA Jan 13 '21 at 10:10
  • Use `where`. As in `->where('period_from', '>=', $dateFrom)->where('period_to', '<=', $dateTo)` – user3532758 Jan 13 '21 at 10:14
  • 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 Jan 13 '21 at 10:27
  • You have first() in your query, maybe that's the reason for single day? Change to get() and check – user3532758 Jan 13 '21 at 10:32
  • I've got `->first()` because I only need the latest result from the returned results – Ryan H Jan 13 '21 at 10:57
  • Well, it doesnt make sense then. Can you share the generated SQL statement then? Enable query log to inspect the query send.. and/or check if the dates align with the records in db. To enable query log: https://stackoverflow.com/questions/41140975/laravel-eloquent-display-query-log – user3532758 Jan 13 '21 at 11:30

2 Answers2

2

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();
Kenath
  • 600
  • 5
  • 13
0

try this one

   $start_date = Carbon::parse($request->start_date)
                         ->toDateTimeString();

   $end_date = Carbon::parse($request->end_date)
                         ->toDateTimeString();

   return User::whereBetween('created_at', [
     $start_date, $end_date
   ])->get();

it helped me , got the information from this article from codegrepper

MostafaHashem
  • 127
  • 2
  • 3