Laravel 8使用Eloquent按年龄差跨两表查询并按分类分组

核心规则校准 #

首先明确和常规年龄计算的差异：本次年龄计算的基准点是当前自然年的12月31日，不是当前实时日期，计算的是从人员出生日期到该基准点的完整整年间隔，不能用简单的年份差值计算，避免把未到生日的年龄算大。

分步实现逻辑 #

• 用SQL原生表达式封装固定计算逻辑，避免把数据拉到PHP层循环计算，大幅提升查询效率
• 采用范围关联的方式匹配对应分类，无需固定外键依赖
• 带上peoples表的有效数据筛选条件（is_visible=1）
• 最终按分类名称完成结果分组

参考代码 #

常规数据量场景（PHP集合层分组） #

适合人员数据量在万级以内的场景，逻辑灵活，后续如果需要对人员字段做格式化处理更方便：

use Illuminate\Support\Facades\DB;

// 生成当年最后一天的日期表达式
$yearEnd = DB::raw("DATE_FORMAT(NOW(), '%Y-12-31')");
// 计算人员符合规则的年龄值
$ageCalc = DB::raw("TIMESTAMPDIFF(YEAR, peoples.date_of_birth, {$yearEnd})");

$groupedData = DB::table('peoples')
    // 左连接关联符合年龄范围的分类，未匹配到分类的人员会保留
    ->leftJoin('categories', function ($join) use ($ageCalc) {
        $join->where($ageCalc, '>=', DB::raw('categories.min_age'))
             ->where($ageCalc, '<=', DB::raw('categories.max_age'));
    })
    // 过滤仅可见的人员数据
    ->where('peoples.is_visible', 1)
    ->select(
        'peoples.id',
        'peoples.name',
        'peoples.date_of_birth',
        // 未匹配到分类的统一标记为未分类
        DB::raw("IFNULL(categories.name, '未分类') as category_name")
    )
    ->get()
    // 按分类名称完成分组
    ->groupBy('category_name');

大数据量场景（SQL层分组） #

适合人员数据量超过十万级的场景，直接在数据库层面完成分组聚合，减少PHP侧的内存占用：

// 复用上面定义的$yearEnd、$ageCalc表达式
$groupedData = DB::table('peoples')
    ->leftJoin('categories', function ($join) use ($ageCalc) {
        $join->where($ageCalc, '>=', DB::raw('categories.min_age'))
             ->where($ageCalc, '<=', DB::raw('categories.max_age'));
    })
    ->where('peoples.is_visible', 1)
    ->select(
        DB::raw("IFNULL(categories.name, '未分类') as category_name"),
        // 直接聚合每个分类下的人员列表为JSON格式
        DB::raw("JSON_ARRAYAGG(JSON_OBJECT('people_id', peoples.id, 'people_name', peoples.name)) as people_list")
    )
    ->groupBy('category_name')
    ->get();

避坑提示 #

• 年龄计算别用年份直接相减：如果写YEAR(今年最后一天) - YEAR(出生日期)，遇到生日在当前时间点之后的（也就是现在还没过当年生日的），年龄会比实际到当年最后一天的间隔多算1岁，和规则要求不符
• 连接方式按需调整：如果业务不需要保留没匹配上分类的人员，把leftJoin换成innerJoin就行
• 提前排查分类区间：如果categories表的min_age、max_age存在区间重叠，会导致同一个人匹配到多个分类、出现重复数据，上线前最好加约束保证区间连续不重叠

内容的提问来源于stack exchange，提问作者sihciv