Baptiste Arnaud Baptiste Arnaud - 1 year ago 56
SQL Question

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
that returns this value:


Any ideas what I'm doing wrong?

Answer Source

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.

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()); 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.


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());