Okay, I'm pulling hair out with this one:
I'm using Laravel/Eloquent and PHP. I have a Cadet model, which has a DATE field for date of birth called birthday.
In a controller, I'm retrieving a Collection of Cadet models that meet various criteria, but I'd like to filter the Collection based on the age of each Cadet too (i.e. the $output Collection should only have Cadet models above a minimum age.)
$activity->age_min is the minimum age (integer) I am wanting to filter the Collection by.
So, this is what I've been trying to do:
$output = $cadets->where('*, TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS age', '>', $activity->age_min);
Problem is, I'm getting an empty array as a result despite knowing that at least two Cadet records fulfill the criteria. If I add ->get(); to the end, the db falls over.
Can anyone point me in the right direction to get this to work so that $output contains a Collection of Cadet models with ages above $activity->age_min?
UPDATE
Okay, well, I've tried btl's suggestions and can't get them to work as I need to work out the age of each cadet and compare it with an integer to decide whether to exclude them or not.
I've had a play with lesssugar's idea below and got this far:
// Work out a date for which people must be born after
$max_date = \Carbon\Carbon::parse($activity->date_from)->subYears($activity->age_min);
// Filter the collection
$output = $cadets->filter(function ($cadet) {
return $cadet->birthday > $max_date;
});
... but I'm going round in circles trying to work out how to use the Filter function and get it to only return Cadets whose birth date is more recent than $max_date.
I think I've been at this for too long today so I'm stepping away for a while. Any help or suggestions (and especially answers!) would be gratefully received! :)
Okay, after some thought this is what I came up with:
// Work out a date for which people must be born BEFORE to be included
$max_date = \Carbon\Carbon::parse($activity->date_from)->subYears($activity->age_min);
// Filter the collection
$cadets = $cadets->filter(function($cadet) use ($max_date) {
if (\Carbon\Carbon::parse($cadet->birthday)->lte($max_date)) {
return true;
}
}
});
Thanks to @lesssugar for the filter() suggestion. Firstly using Carbon it's easy to create a date (minus the age limit) that is the threshold for determining if a cadet is old enough or not to be included in the collection ($max_date).
Then I run a filter on the existing collection and only pick out the records I'm interested in that have a birthday less than or equal to $max_date.
Originally I was thinking I could do this in a single query, but this is the solution I've arrived at and hopefully isn't too much more work.
Use Carbon, it can handle these kind of date comparisons and so much easily.
$cadets = Cadet::where(Carbon::parse('birthday'), '>=', $activity->age_min)->get();
or
$cadets = Cadet::all()->each(function($cadet) use ($activity) {
return Carbon::parse($cadet->birthday)->gt(Carbon::parse($activity->age_min);
});