如何使用laravel eloquent查询用户并按年龄范围对他们进行分组

Question

我想查询用户并按年龄范围对他们进行分组

这是我到目前为止所做的

User::applicant()->get()

                ->groupBy(function ($item) {

                    return Carbon::parse($item->dob)->age;

                })

                ->map(function ($collection) {

                    return $collection->count();

                });

这是我从上面的查询中得到的

现在想按年龄范围获取集合和订单

18-24: 1,

25-35: 5,

36-45: 89,

46+ : 84

Answer 1

我将使用map()and 的组合mapToGroups()，我很确定一定有一种更简单的方法，但这对我来说很有趣：$ranges = [ // the start of each age-range.    '18-24' => 18,    '25-35' => 25,    '36-45' => 36,    '46+' => 46];$output = User::applicant()    ->get()    ->map(function ($user) use ($ranges) {        $age = Carbon::parse($user->dob)->age;        foreach($ranges as $key => $breakpoint)        {            if ($breakpoint >= $age)            {                $user->range = $key;                break;            }        }        return $user;    })    ->mapToGroups(function ($user, $key) {        return [$user->range => $user];    })    ->map(function ($group) {        return count($group);    })    ->sortKeys();dd($output);这背后的想法是为每个记录添加一个属性，其值对应于其年龄范围，然后按此键对它们进行分组，创建一组按范围分组的用户数组，最终计算此键内每个子数组的元素。这应该返回如下内容：=> Illuminate\Support\Collection {#2948     all: [       "25-35" => 1,       "36-45" => 2,       "46+" => 1,     ],   }

Answer 2

这是一个未经测试的解决方案（需要重构：$groups = ['18-24' =>, '25-35', ..., '45'];$applicants = User::applicant()->get();$groups = collect($groups)&nbsp; &nbsp; ->map(function ($range, $key) use ($applicants) {&nbsp; &nbsp; &nbsp; &nbsp; $rangeLimits = explode('-', $range);&nbsp; &nbsp; &nbsp; &nbsp; $count = $applicants->filter(function ($applicant, $key) use ($rangeLimits) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $age = Carbon::parse($applicant->dob)->age;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $verdict = $age >= $rangeLimits[0];&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (isset($rangeLimits[1])) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $verdict = $age <= $rangeLimits[1];&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return $verdict&nbsp; &nbsp; &nbsp; &nbsp; })->count();&nbsp; &nbsp; &nbsp; &nbsp; $range = ! isset($rangeLimits[1]) ? $range . '+' : $range;&nbsp; &nbsp; &nbsp; &nbsp; return [ $range => $count ];&nbsp;&nbsp; &nbsp; })->flatten()->all();首先，您需要创建一组您需要的组，最后一组不应有+.然后你会得到所有的申请者。然后，您遍历每个组，找出申请人是否在其范围内，并获得计数。这被映射到结果数组。

Answer 3

试试这个$users =&nbsp; \DB::table('users')&nbsp; &nbsp; &nbsp; ->select(DB::raw('concat(10*floor(age/10), \'-\', 10*floor(age/10) + 9) as `range`, count(*) as `numberofusers`'))&nbsp; &nbsp; &nbsp; ->groupBy('range')&nbsp; &nbsp; &nbsp; ->get();