如何基于order列最大值对应的证书ID筛选Swimmer？

场景说明 #

我不太擅长复杂数据库查询，现在遇到一个难题：需要根据特定证书ID筛选对应的游泳者，但匹配规则是——该证书必须是这个游泳者的最高等级证书（等级高低由Certificate表的order字段决定，值越大等级越高）。

证书体系可能后续在已有等级之间新增证书（比如初始只有Level 1、Level 2，后来新增Level 1.1、1.2、1.3），所以用order字段来定义证书的获取先后和等级高低。

数据表关联关系 #

• Swimmer表：核心字段id（主键，游泳者ID）
• SwimmerCertificate表：关联字段swimmer_id（关联Swimmer.id）、certificate_id（关联Certificate.id），用于记录游泳者持有的证书
• Certificate表：核心字段id（主键，证书ID）、order（等级排序值）、name（证书名称）

示例数据 #

# 游泳者与证书的关联关系
Swimmer One 持有证书ID：[1, 2, 3, 4]
Swimmer Two 持有证书ID：[1, 2, 4]

# 各证书的order值（越大等级越高）
$certificates = [
    1 => ['order' => 1],
    2 => ['order' => 2],
    3 => ['order' => 4], // 等级最高
    4 => ['order' => 3]
];

根据规则的筛选结果：

• 筛选证书ID=4时，仅Swimmer Two符合（Swimmer One的最高证书是ID=3，order值更大）
• 筛选证书ID=3时，仅Swimmer One符合

解决方案1：直接通过数据库查询实现 #

SQL查询语句 #

可以通过子查询先获取每个游泳者的最高order值，再关联筛选出对应证书ID的游泳者：

SELECT s.*
FROM Swimmer s
JOIN SwimmerCertificate sc ON s.id = sc.swimmer_id
JOIN Certificate c ON sc.certificate_id = c.id
-- 替换这里的4为你要筛选的目标证书ID
WHERE sc.certificate_id = 4
AND c.order = (
    SELECT MAX(c2.order)
    FROM SwimmerCertificate sc2
    JOIN Certificate c2 ON sc2.certificate_id = c2.id
    WHERE sc2.swimmer_id = s.id
);

基于Laravel Eloquent的查询实现 #

如果用Laravel框架，可以这样写：

$targetCertId = 4; // 替换为目标证书ID

$swimmers = Swimmer::whereHas('certificates', function ($query) use ($targetCertId) {
    $query->where('id', $targetCertId)
          ->where('order', function ($subquery) {
              $subquery->select(DB::raw('MAX(c.order)'))
                       ->from('certificates as c')
                       ->join('swimmer_certificate as sc', 'c.id', '=', 'sc.certificate_id')
                       ->whereColumn('sc.swimmer_id', 'swimmers.id');
          });
})->get();

解决方案2：新增字段维护当前最高证书 #

这也是我最终选择的方案——在Swimmer表中新增current_certificate_id字段，通过模型观察者自动维护这个字段的值。

步骤说明 #

1. 给Swimmer表添加current_certificate_id字段，关联Certificate表的id字段
2. 创建SwimmerCertificate模型的观察者，监听证书关联的新增、修改、删除事件
3. 在事件回调中，重新计算对应游泳者的最高等级证书，并更新current_certificate_id

Laravel观察者示例代码 #

class SwimmerCertificateObserver
{
    // 监听证书关联的保存（新增/更新）事件
    public function saved(SwimmerCertificate $swimmerCertificate)
    {
        $swimmer = $swimmerCertificate->swimmer;
        // 获取该游泳者的最高等级证书
        $highestCert = $swimmer->certificates()->orderBy('order', 'desc')->first();
        // 更新当前最高证书ID
        $swimmer->current_certificate_id = $highestCert?->id;
        $swimmer->save();
    }

    // 监听证书关联的删除事件
    public function deleted(SwimmerCertificate $swimmerCertificate)
    {
        $this->saved($swimmerCertificate);
    }
}

后续筛选方式 #

维护字段后，筛选会变得非常简单：

SELECT * FROM Swimmer WHERE current_certificate_id = 4;

或者Laravel Eloquent：

$swimmers = Swimmer::where('current_certificate_id', $targetCertId)->get();

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