Level 61
You need return after the last if statement in the scopeOfAgeRange method. Just return the $query variable.
return $query;
Feb 9, 2021
6
Level 24
Stuck on query age range
can anyone help for check the logic of scopeOfAgeRange is correct, Thx
Controller
class SearchStaff extends Controller
{
public function __invoke(Request $request)
{
$staffs = Staff::ofFilter($request)->get();
return view('staffs.index', compact('staffs'));
}
}
Model
class Staff extends Model
{
public function scopeOfFilter($query, $request)
{
return $query->ofGender($request->gender)
->ofAgeRange($request->from_age, $request->to_age);
}
public function scopeOfGender($query, $gender = null)
{
if ($gender === '0' || $gender === '1') {
return $query->where('gender', $gender);
}
}
public function scopeOfAgeRange($query, $fromAge = null, $toAge = null)
{
$fromAge = is_string($fromAge) ? intval($fromAge) : 0;
$toAge = is_string($toAge) ? intval($toAge) : 0;
$minAge = min([$fromAge, $toAge]);
$maxAge = max([$fromAge, $toAge]);
if ($minAge === 0) {
$minAge = $maxAge;
}
if ($minAge > 0 && $maxAge > 0) {
return $query->whereBetween('birth_date', [
// small value eg. 1970-04-03
now()->subYears($maxAge + 1)->format('Y-m-d'),
// large value eg. 1980-04-03
now()->subYears($minAge)->format('Y-m-d'),
]);
}
}
}
scopeOfAgeRange possible combination
$fromAge | $toAge |
---------------------------------
0 | 0 | Pass - no query
35 | 29 | Pass - query between 35+1,29
29 | 35 | Pass - query between 35+1,29
0 | 35 | Pass - query between 35+1,35
35 | 0 | Pass - query between 35+1,35
Level 24
@bugsysha did you means like this
if ($minAge > 0 && $maxAge > 0) {
...
}
return $query;
what is the difference ? even without return $query;, it also return the Builder, i change the order, and it is chainable too
public function scopeOfFilter($query, $request)
{
return $query->ofAgeRange($request->from_age, $request->to_age)
->ofGender($request->gender);
}
Level 80
@newbie360 You just need to use the ages to create date-time instances, and then pass that to a whereBetween clause on your birth_date column.
So, given:
Staff::ofAgeRange(18, 30)->get();
The scopeOfAgeRange method could look like this:
public function scopeOfAgeRange(Builder $query, int $from, int $to)
{
if ($from > $to) {
throw new InvalidArgumentException(
sprintf('The to value [%d] should be greater than from [%d]', $to, $from)
);
}
$from = Carbon::now()->subYears($from);
$to = Carbon::now()->subYears($to);
return $query->whereBetween('birth_date', [$from, $to]);
}
Level 24
@martinbean thx for your reply
without ->format() it also work when the birth_date column type is date not timestamp ?
Carbon::now()->subYears($from);
in table is storing the birth_date in this format
1970-04-13
1985-01-29
in the view have two age range input box, my logic is for not care which input is larger than other one, the code will check which is max and min, so the client may input
empty | empty
empty | 35
35 | 35
35 | empty
35 | 18
18 | 35
and your logic seems something wrong?
$from = Carbon::now()->subYears(30); // 2021 - 30 = 1991
$to = Carbon::now()->subYears(35); // 2021 - 35 = 1986
// whereBetween first param should be smaller than second param
return $query->whereBetween('birth_date', [$from, $to]);
Level 80
@newbie360 Just swap the $from and the $to around in that case. I wrote the answer “off the cuff” but yeah, you’d want the oldest date first in the whereBetween call.
Level 24
birth_date
-----------
1979-11-02
https://www.calculator.net/age-calculator.html
Age is 41 years 3 months 7 days
if we direct use client input 41, lets see what happen
dd(now()->subYears(41)->format('Y-m-d'));
// "1980-02-09"
select * from staffs where `birth_date` between "1980-02-09" and "..."
// no records
for my code $maxAge + 1 is correct?
return $query->whereBetween('birth_date', [
now()->subYears($maxAge + 1)->format('Y-m-d'),
now()->subYears($minAge)->format('Y-m-d'),
]);
select * from staffs where `birth_date` between "1979-02-09" and "..."
Please or to participate in this conversation.