如何用Doctrine/SQL从Actions表查询当前处于on ice状态的球员？

Hey there! Let's break down how to solve this problem step by step. You need to find players who are currently "on ice"—meaning they've had a "gets on ice" event in the current game, but no corresponding "gets off ice" event after that.

核心思路 #

There are two solid approaches to tackle this:

• Track the latest action per player: For each player in the game, find their most recent event. If that event is "gets on ice", they're currently on the ice.
• Exclude players who've already gotten off: Find all players who have a "gets on ice" record, then exclude anyone who has a later "gets off ice" record in the same game.

SQL查询语句 #

Let's assume your Actions table has these key fields: id (auto-incrementing, or use timestamp for exact timing), player_id, action_type, game_id, and timestamp (to order events chronologically). Replace placeholders like [当前游戏ID] with your actual game identifier.

方法一：窗口函数（推荐，高效） #

This uses ROW_NUMBER() to rank each player's events by time (newest first), then filters for players whose latest event is "gets on ice":

SELECT player_id, player_name -- 替换为你的球员字段（如果关联了Players表）
FROM (
    SELECT 
        a.player_id,
        p.player_name, -- 如果有Players表，这里关联查询获取详细信息
        a.action_type,
        a.timestamp,
        ROW_NUMBER() OVER (PARTITION BY a.player_id ORDER BY a.timestamp DESC) AS rn
    FROM Actions a
    LEFT JOIN Players p ON a.player_id = p.id -- 可选，关联球员表
    WHERE a.game_id = [当前游戏ID]
) ranked_actions
WHERE rn = 1 AND action_type = 'gets on ice';

方法二：左连接子查询 #

This approach finds all "gets on ice" records and excludes those that have a corresponding later "gets off ice" event:

SELECT DISTINCT a_on.player_id, p.player_name
FROM Actions a_on
LEFT JOIN Players p ON a_on.player_id = p.id
LEFT JOIN Actions a_off 
    ON a_on.player_id = a_off.player_id
    AND a_off.action_type = 'gets off ice'
    AND a_off.timestamp > a_on.timestamp
    AND a_off.game_id = a_on.game_id
WHERE a_on.action_type = 'gets on ice'
    AND a_on.game_id = [当前游戏ID]
    AND a_off.player_id IS NULL;

Symfony/Doctrine实现 #

Here's how to translate these queries into Doctrine QueryBuilder for your Symfony 3 project.

用窗口函数的方式 #

Note: Make sure your Doctrine ORM version supports window functions (Doctrine 2.6+ works, which is compatible with Symfony 3).

// 在你的ActionsRepository.php中
public function getCurrentOnIcePlayers($gameId)
{
    // 第一步：获取带排名的所有动作记录
    $rankedQuery = $this->createQueryBuilder('a')
        ->select('a.player, a.actionType, a.timestamp')
        ->addSelect('ROW_NUMBER() OVER (PARTITION BY a.player ORDER BY a.timestamp DESC) AS rn')
        ->where('a.game = :gameId')
        ->setParameter('gameId', $gameId)
        ->getQuery();

    $rankedActions = $rankedQuery->getResult();

    // 第二步：过滤出最新动作是上场的球员
    $onIcePlayers = [];
    foreach ($rankedActions as $action) {
        if ($action['rn'] === 1 && $action['actionType'] === 'gets on ice') {
            $onIcePlayers[] = $action['player'];
        }
    }

    // 去重（防止极端情况重复）
    $onIcePlayers = array_unique($onIcePlayers, SORT_REGULAR);

    return $onIcePlayers;
}

用子查询的方式 #

This uses a NOT EXISTS clause to exclude players who've gotten off ice:

// 在你的ActionsRepository.php中
public function getCurrentOnIcePlayers($gameId)
{
    // 构建子查询：检查球员是否有后续的下场动作
    $subQuery = $this->createQueryBuilder('a_off')
        ->select('1')
        ->where('a_off.player = a_on.player')
        ->andWhere('a_off.actionType = :offAction')
        ->andWhere('a_off.timestamp > a_on.timestamp')
        ->andWhere('a_off.game = :gameId');

    // 主查询：获取所有上场且无后续下场的球员
    $qb = $this->createQueryBuilder('a_on')
        ->select('DISTINCT a_on.player')
        ->where('a_on.actionType = :onAction')
        ->andWhere('a_on.game = :gameId')
        ->andWhere($qb->expr()->not($qb->expr()->exists($subQuery->getDQL())))
        ->setParameters([
            'onAction' => 'gets on ice',
            'offAction' => 'gets off ice',
            'gameId' => $gameId,
        ]);

    return $qb->getQuery()->getResult();
}

注意事项 #

• Timing is key: Ensure your Actions table has a reliable way to order events (like a timestamp field or auto-incrementing id). Without this, you can't accurately determine which action came first.
• Player table association: If your player data is in a separate Players table, make sure to join it in your queries to get full player details instead of just IDs.
• Test first: Run the raw SQL queries directly against your database first to verify the results, then integrate them into Doctrine to avoid unexpected issues.

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