AGE range using Carbon and Eloquent

Hi.

I want to find some people by AGE range.

This is ok:

$from = Carbon::now()->subYears($params['age'][1]);
$to = Carbon::now()->subYears($params['age'][0]);

$users = User::whereBetween('birth_date', [$from, $to])->get();

// generated sql
// SELECT * FROM `users` WHERE `birth_date` between '1990-10-15' and '2001-10-15'

The problem is that if I the range is equal, the sql will search the same date, and not in a range.

I could check if the age is the same an add (1 year - 1day) but is there a better way?

Thx.

martinbean

10 years ago

Level 80

@luddinus I’m not sure what you mean by, “if the range is equal, the sql will search the same date, and not in a range.” Do you mean if the user submitted the same date for both the start and end date?

I’d maybe make a query scope that has one required parameter, and an optional second parameter. If the second parameter is null (or the same as the first parameter) you could add a basic WHERE clause; if both parameters are specified and different, then you could search by range:

public function scopeWhereBirthDateIs($query, $start, $end = null)
{
    if (is_null($end) || $end == $start) {
        return $query->where('birth_date', '=', $start);
    }
    return $query->whereBetween('birth_date', $start, $end);
}

Then usage:

User::whereBirthDateIs('1989-03-13');
User::whereBirthDateIs('1989-03-13', '1994-05-01');

luddinus

10 years ago

Level 6

@martinbean more or less.

$start and $end will be numeric (age), so if the $start_age and $end_age is the same (e.g. 18), the sql generated will be a concrete day and no a age range (People who is 18), because this is what I'm doing:

Carbon::today()->subYears(18); // October 15, 1997
Carbon::today()->subYears(19); // October 15, 1998

Actually this is wrong, because If I want people who is 18 OR 19, the generated query only will show all of people that is 18 (or 19 but only if his birth date is today)

Maybe I need to rest and think about this later!

martinbean

10 years ago

Best Answer

Level 80

@luddinus Gotcha. In that case, you want something like this:

public function scopeAgedBetween($query, $start, $end = null)
{
    if (is_null($end)) {
        $end = $start;
    }

    $now = $this->freshTimestamp();
    $start = $now->subYears($start);
    $end = $now->subYears($end)->addYear()->subDay(); // plus 1 year minus a day

    return $query->whereBetween('birth_date', $start, $end);
}

As a forewarning: this was written off-the-cuff.

What it’ll do is allow you to pass either one or two arguments. For the end date, it’ll add a year and then subtract a day. So if you pass 25 as an argument, it’ll search for birth dates between 15 October 1989 and 14 October 1990 (as any one born on 15 October 1990 would be 26—happy birthday!)

Usage remains similar:

$users = User::agedBetween(25)->get();
$users = User::agedBetween(18, 30)->get();

1 like

luddinus

10 years ago

Level 6

@martinbean Thx, I guess I have to do the same with the start_date (minus 1 year, plus 1 day)

Please or to participate in this conversation.