Baptiste Arnaud Baptiste Arnaud - 3 months ago 9
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
Carbon::now()->toDateString()
that returns this value:

2016-08-19


Any ideas what I'm doing wrong?

Answer

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