Laravel中使用when()实现小组赛排名（含积分及交手决胜）

嘿，我来帮你搞定这个小组赛积分排名的逻辑！咱们先理清楚核心需求：先按胜3分、负0分、平局1分算出各队总积分，一旦出现积分相同的队伍，就用小组内直接交手记录来重新排序，决出积分最高的队伍。下面我用Laravel的when()方法来实现这个灵活的查询逻辑。

第一步：先搞定基础积分计算 #

假设咱们有teams表存队伍信息，matches表存比赛记录（包含home_team_id、away_team_id、home_score、away_score、group_id这些字段），先写基础查询算出每个队伍的总积分：

$groupId = 1; // 替换成你当前的小组ID

// 基础积分查询：计算每个队伍的总积分
$teamsQuery = Team::where('group_id', $groupId)
    ->select([
        'teams.id',
        'teams.name',
        // 计算总积分：胜场3分 + 平局1分
        DB::raw('(
            SUM(CASE WHEN home_team_id = teams.id AND home_score > away_score THEN 3 ELSE 0 END) +
            SUM(CASE WHEN away_team_id = teams.id AND away_score > home_score THEN 3 ELSE 0 END) +
            SUM(CASE WHEN (home_team_id = teams.id OR away_team_id = teams.id) AND home_score = away_score THEN 1 ELSE 0 END)
        ) AS total_points')
    ])
    ->join('matches', function ($join) use ($groupId) {
        $join->where(function ($query) {
            $query->whereColumn('matches.home_team_id', 'teams.id')
                  ->orWhereColumn('matches.away_team_id', 'teams.id');
        })->where('matches.group_id', $groupId);
    })
    ->groupBy('teams.id', 'teams.name')
    ->orderBy('total_points', 'desc');

第二步：用when()处理积分相同的决胜场景 #

接下来要判断：如果查询结果里有积分相同的队伍，就触发交手记录的计算逻辑。这里的关键是，只在需要的时候（积分相同）才执行复杂的交手积分计算，when()刚好能帮我们实现这个条件性的查询扩展：

// 先判断是否存在积分相同的队伍
$hasSamePoints = $teamsQuery->get()->groupBy('total_points')
    ->filter(fn($group) => $group->count() > 1)
    ->isNotEmpty();

// 用when()添加交手记录排序逻辑
$finalTeams = $teamsQuery->when($hasSamePoints, function ($query) use ($groupId) {
    return $query->addSelect([
        // 计算当前队伍与其他积分相同队伍的交手积分
        'head_to_head_points' => DB::raw('(
            SELECT SUM(
                CASE
                    WHEN m.home_team_id = teams.id AND m.home_score > m.away_score THEN 3
                    WHEN m.away_team_id = teams.id AND m.away_score > m.home_score THEN 3
                    WHEN m.home_score = m.away_score THEN 1
                    ELSE 0
                END
            )
            FROM matches m
            WHERE m.group_id = ' . $groupId . '
            AND (
                -- 匹配当前队伍作为主场，对手是积分相同的队伍
                (m.home_team_id = teams.id AND m.away_team_id IN (
                    SELECT t.id FROM teams t
                    JOIN matches mt ON (mt.home_team_id = t.id OR mt.away_team_id = t.id)
                    WHERE mt.group_id = ' . $groupId . '
                    GROUP BY t.id, t.name
                    HAVING (
                        SUM(CASE WHEN mt.home_team_id = t.id AND mt.home_score > mt.away_score THEN 3 ELSE 0 END) +
                        SUM(CASE WHEN mt.away_team_id = t.id AND mt.away_score > mt.home_score THEN 3 ELSE 0 END) +
                        SUM(CASE WHEN (mt.home_team_id = t.id OR mt.away_team_id = t.id) AND mt.home_score = mt.away_score THEN 1 ELSE 0 END)
                    ) = (
                        SUM(CASE WHEN mt.home_team_id = teams.id AND mt.home_score > mt.away_score THEN 3 ELSE 0 END) +
                        SUM(CASE WHEN mt.away_team_id = teams.id AND mt.away_score > mt.home_score THEN 3 ELSE 0 END) +
                        SUM(CASE WHEN (mt.home_team_id = teams.id OR mt.away_team_id = teams.id) AND mt.home_score = mt.away_score THEN 1 ELSE 0 END)
                    )
                ))
                -- 匹配当前队伍作为客场，对手是积分相同的队伍
                OR (m.away_team_id = teams.id AND m.home_team_id IN (
                    SELECT t.id FROM teams t
                    JOIN matches mt ON (mt.home_team_id = t.id OR mt.away_team_id = t.id)
                    WHERE mt.group_id = ' . $groupId . '
                    GROUP BY t.id, t.name
                    HAVING (
                        SUM(CASE WHEN mt.home_team_id = t.id AND mt.home_score > mt.away_score THEN 3 ELSE 0 END) +
                        SUM(CASE WHEN mt.away_team_id = t.id AND mt.away_score > mt.home_score THEN 3 ELSE 0 END) +
                        SUM(CASE WHEN (mt.home_team_id = t.id OR mt.away_team_id = t.id) AND mt.home_score = mt.away_score THEN 1 ELSE 0 END)
                    ) = (
                        SUM(CASE WHEN mt.home_team_id = teams.id AND mt.home_score > mt.away_score THEN 3 ELSE 0 END) +
                        SUM(CASE WHEN mt.away_team_id = teams.id AND mt.away_score > mt.home_score THEN 3 ELSE 0 END) +
                        SUM(CASE WHEN (mt.home_team_id = teams.id OR mt.away_team_id = teams.id) AND mt.home_score = mt.away_score THEN 1 ELSE 0 END)
                    )
                ))
            )
        )')
    ])->orderBy('head_to_head_points', 'desc'); // 按交手积分降序排序
})->get();

逻辑说明 #

• when()的优势在于按需执行查询逻辑：只有当存在积分相同的队伍时，才会加载复杂的交手积分计算，避免不必要的性能消耗。
• 子查询里的核心是：筛选出和当前队伍积分相同的所有对手，然后计算两队直接交手的积分，最后用这个交手积分来重新排名。
• 如果后续需要更细致的决胜规则（比如交手净胜球、总进球数），可以在when()的闭包里继续添加对应的计算字段和排序条件。

简化小技巧 #

要是觉得子查询太啰嗦，可以把积分计算逻辑封装成Laravel查询作用域或者数据库视图，比如创建一个team_group_points视图预计算各队积分，这样后续查询就能直接引用，代码会整洁很多。

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