Eloquent "whereDate" not working after Laravel 5.4 Upgrade

Issue

(sorry in advance for the long post)

I am in the process of upgrading my application from laravel 5.2 to 5.4, and have been stuck on this problem since this morning. For some reason my query to select upcoming business events with the Eloquent ‘whereDate’ is not returning anything, even though everything else points to the fact that it should be working!

I have a phpUnit test that won’t pass to try and localize the problem. (See further below)

I have a ‘Business‘ model:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Business extends Model
{
    protected $table = 'businesses';
    ..

public function businessEvents(){
    return $this->hasMany('App\BusinessEvent', 'business_id');
}

As well as a ‘BusinessEvent‘ model:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class BusinessEvent extends Model
{
  protected $table = 'business_events';
  protected $fillable = ['business_id', ..., 'start_date', 'end_date'];

  public function business(){
      return $this->belongsTo('App\Business', 'business_id');
  }
...

Both of which I make dummy versions of in my test. The dummy ‘business’ works for other tests, but I introduced the following dummy ‘business_event’ for this test case:

protected function createMockBusinessEventWithUpcomingDateTime($businessId){
  $faker = Faker::create();

  $businessEvent = BusinessEvent::create([
      'business_id'   => $businessId,
      'name'          => $faker->name,
      'description'   => $faker->text,
      'start_date'    => date("Y-m-d H:i:s", strtotime(date("Y-m-d H:i:s")."+30 minutes")),
      'end_date'      => date("Y-m-d H:i:s", strtotime(date("Y-m-d H:i:s")."+1 hour +30 minutes"))
  ]);

  ...

  $business = Business::find($businessId);
  if($business){
    $businessEvent->business()->associate($business);
  }

  return $businessEvent;
}

Now here is the actual test function:

public function testGetUpcomingN(){

    $this->businessEvent  = $this->createMockBusinessEventWithUpcomingDateTime($this->business->id);

    $response = $this->json('GET', '/api/v1/business/'.$this->business->id.'/events/upcoming/2');

    $this->outp($response->content());

    $this->outp("Testing Assertions");
    $this->assertEquals(200, $response->status());  
    $this->assertEquals($this->business->businessEvents()->first()->id, $this->businessEvent->id);     
}

The test doesn’t fail, which means that the event is correctly being associated with the business, and no matter how I query the api endpoint (HttpRequester, Logging result, returned value in test, production…) it always returns an empty array!

The following function is what is called in the controller (also verified!):

public function upcomingN($businessId, $n){
    Log::info('Get upcoming '.$n.' recorded events for business with id: '.$businessId);
    $business = Business::find($businessId);
    if (is_null($business)){
        Log::warning('No business exists with given id. Exiting.');
        return response('No Content.', 204);
    }

    $currentDate = date('Y-m-d H:i:s');

    $greaterThan = $business->businessEvents()->first()->end_date >= $currentDate;
    Log::info("Greater THAN:", [$greaterThan]);

    $events = $business->businessEvents()->whereDate('end_date', '>=', $currentDate)->take($n)->get();

    if (!is_null($events)){
        Log::info('Got upcoming N recorded events for business with id: '.$businessId, (array) $events);
        return response()->json($events);
    }

    return response('No Content.', 204);
}

As you can see, I even tried logging the “$greaterThan” boolean that should test the exact same thing that “whereDate” does, and it evaluates to true! I have tried Carbon::parse()-ing, setting the locale, and now I am pretty much out of ideas.

The old function that worked in 5.2:

public function upcomingN($businessId, $n){
   Log::info('Get upcoming N recorded events for business with id: '.$businessId);
$business = Business::find($businessId);
if (is_null($business)){
    Log::warning('No business exists with given id. Exiting.');
        return response('No Content.', 204);
    }

    $currentDate = date('Y-m-d H:i:s');
    $events = $business->businessEvents()->whereDate('start_date', '>=', $currentDate)->take($n)->get();

     if (!is_null($events)){
         Log::info('Got upcoming N recorded events for business with id: '.$businessId, (array) $events);
         return response()->json($events);
     }

    return response('No Content.', 204);
}

One thing I did have to change during the upgrade was setting the mysql-db to non-strict, because Laravel was complaining about a lack of default values. However, I’ve checked the db and the test entries are correctly populated.

Any help would be greatly appreciated!

Solution

Thanks so much to Omisakin Oluwatobi, the solution was in fact using an Eloquent ‘where’ instead of ‘whereDate’, as ‘whereDate’ only compares the dates and not the date times (which, in hindsight seems obvious).

I find it a bit confusing that the ‘whereDate’ function exists if you can achieve the same result with ‘where’ using the same amount of code.

I can verify (unit-tested) that the following final solution works for selecting future datetimes and ignoring past ones:

public function upcomingN($businessId, $n){
    Log::info('Get upcoming '.$n.' recorded events for business with id: '.$businessId);
    $business = Business::find($businessId);

    if (is_null($business)){
        Log::warning('No business exists with given id. Exiting.');
        return response('No Content.', 204);
    }

    $currentDate = date('Y-m-d H:i:s');
    $events = $business->businessEvents()->where('end_date', '>=', $currentDate)->take($n)->get();

    if (!is_null($events)){
        Log::info('Got upcoming N recorded events for business with id: '.$businessId, (array) $events);
        return response()->json($events);
    }

    return response('No Content.', 204);
}

Answered By – bnunamak

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published