Search code examples
sqllaraveleloquentphp-carbon

Date compare Eloquent 'where' method doesn't work

I'm desperately trying to sort an SQL request:

I want to have every row where the date attribute is less than the actual date.

Here's what I am doing:

$student = User::find($id)->student;
$extras = $student->extras->where('find', 1)
                          ->where('date', '<', Carbon::now()->toDateString());

It returns an empty Collection. That's weird because $student->extras->where('find', 1) returns this :

Collection {#229 ▼
  #items: array:1 [▼
    0 => Extra {#236 ▼
      #fillable: array:10 [▶]
      #connection: null
      #table: null
      #primaryKey: "id"
      #keyType: "int"
      #perPage: 15
      +incrementing: true
      +timestamps: true
      #attributes: array:14 [▼
        "id" => 4
        "broadcast" => 0
        "type" => "Cuisine"
        "date" => "2016-06-15"
        "date_time" => "13:00:00"
        "duration" => 2
        "salary" => 500
        "benefits" => "test2"
        "requirements" => "test2"
        "informations" => ""
        "find" => 1
        "professional_id" => 2
        "created_at" => "2016-08-19 08:18:59"
        "updated_at" => "2016-08-19 08:18:59"
      ]

As you can see, the date is way less than Carbon::now()->toDateString() that returns this value: 

2016-08-19

Any ideas what I'm doing wrong?

Solution

  • Remember that when you access a relation using the property (e.g $student->extras) the results have already been pulled from the database and your using the Illuminate\Database\Eloquent\Collection class to filter the data down. https://github.com/laravel/framework/blob/5.2/src/Illuminate/Support/Collection.php#L282

    If you want the database to query the results you need to use the method of your relation. $student->extras()->where('find', 1)->where('date', '<', Carbon::now())->get(); Note also that I have not called the ->toDateString() method on the carbon object, by doing this the query builder will convert the date to the correct format needed for your database platform.

    Edit:

    If you really wanted to use the collection to handle this you'd need to do something like the following

    $extras = $student->extras->filter(function($extra)
{
    return $extra->date->lt(Carbon::now());
});