如何通过单条查询实现Laravel模型多条件匹配（佣金规则场景）

问题背景 #

我正在开发一个Laravel应用，用于记录公司销售代表的已赚取佣金。每完成一笔销售，就会在scores数据表（对应Score模型）中创建一条记录。由于佣金计算方式可能随时变更，我还设有commission_rules数据表，用于存储佣金规则的生效起止日期及对应佣金。

数据表结构 #

scores表 #

commission_rules表 #

需求 #

希望通过单条查询获取包含scores.id和匹配到的commission_rules.id的数组，要求：

• 当一条销售记录匹配到多条佣金规则时，返回id最大的规则
• 若无匹配规则则返回null
• 预期返回格式：

[
  [ 'id' => 1, 'commission_rule_id' => 1],
  [ 'id' => 2, 'commission_rule_id' => 3],
  [ 'id' => 3, 'commission_rule_id' => 3],
  [ 'id' => 4, 'commission_rule_id' => null]
]

当前做法及问题 #

目前通过遍历所有score记录，为每条记录单独查询匹配的佣金规则，单条查询的Eloquent代码如下：

ValueRule::query()
    ->where('start', '<=', $score->scored_at)
    ->where(function ($query) use ($score) {
        $query->where('end', '>=', $score->scored_at)
              ->orWhere('end', '=', null);
    })
    ->orderBy('id', 'DESC')
    ->first();

但scores表有1万+条记录，这种N+1查询方式耗时过长。尝试过左连接，但会返回每个匹配项对应的行，不符合需求。

解决方案 #

方法1：子查询直接关联最新规则ID #

利用Laravel的子查询功能，直接在主查询中为每条销售记录匹配到最新的佣金规则ID：

$results = Score::select([
    'scores.id',
    DB::raw('(
        SELECT cr.id
        FROM commission_rules cr
        WHERE cr.start <= scores.scored_at
        AND (cr.end >= scores.scored_at OR cr.end IS NULL)
        ORDER BY cr.id DESC
        LIMIT 1
    ) AS commission_rule_id')
])->get()->toArray();

这种方式将N次查询合并为一次主查询，数据库层面的优化会大幅提升效率，建议给commission_rules表的start、end、id字段添加复合索引。

方法2：JOIN结合分组筛选最新规则 #

通过子查询先筛选出每个规则组内ID最大的记录，再与销售表关联：

$maxRuleSubquery = DB::table('commission_rules as cr')
    ->select('cr.*')
    ->orderBy('cr.id', 'DESC')
    ->groupBy('cr.start', 'cr.end');

$results = Score::leftJoinSub($maxRuleSubquery, 'cr', function ($join) {
    $join->on('cr.start', '<=', 'scores.scored_at')
         ->where(function ($query) {
             $query->where('cr.end', '>=', 'scores.scored_at')
                   ->orWhereNull('cr.end');
         });
})->select('scores.id', 'cr.id as commission_rule_id')
  ->get()->toArray();

索引优化建议 #

为了进一步提升查询速度，在commission_rules表创建复合索引：

CREATE INDEX idx_commission_rules_start_end_id ON commission_rules (start, end, id DESC);

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