mysql - Rails 中的复杂 MySQL 查询

Question

我们有关于 NCAA 运动员以及这些运动员上高中的信息。我们想根据参加上述高中的 NCAA 运动员人数对高中进行排名。

我们有players、teams、team_histories、accounts 和player_to_team_histories . account 代表学校(名称、位置、类型(大学、高中))，team 描述account 中的特定团队(男子足球、女子 Volley )，team_history 代表特定球队(2012 年男子足球队)的一年，player 代表传记运动员的信息(他们长大的地方，他们就读的高中，他们的名字)，player_to_team_history 代表 team_history 上的player(年份、尺寸、重量、位置的统计数据)。

我已经制定了以下 MySQL 查询来提取特定大学每所高中的运动员人数排名。我将从最内层的语句开始分解下面的查询:

SELECT WrappedQuery.rank FROM
(SELECT 
    @rownum := @rownum+1 AS rank, q.Name, q.id
FROM    
    (SELECT @rownum := 0) counter, 
    (SELECT 
        Accounts.id, Accounts.Name, COUNT(Accounts.Name) AS count
    FROM
        player_to_team_histories
    INNER JOIN team_histories ON team_histories.id = player_to_team_histories.team_history_id
    INNER JOIN teams ON teams.id = team_histories.team_id
    INNER JOIN accounts ON accounts.id = teams.account_id
    WHERE
        accounts.AccountTypeId = 1 AND player_id IN (SELECT 
            player_id
        FROM
            player_to_team_histories
        WHERE
            player_to_team_histories.not_valid IS NULL AND team_history_id = (SELECT 
                team_history_id
            FROM
                player_to_team_histories
            INNER JOIN team_histories ON team_histories.id = player_to_team_histories.team_history_id
            WHERE
                player_to_team_histories.id = 574651))
    GROUP BY Accounts.Name
    ORDER BY count DESC) q) WrappedQuery
WHERE WrappedQuery.id = 7661

团队历史 ID

SELECT 
    team_history_id
FROM
    player_to_team_histories
        INNER JOIN
    team_histories ON team_histories.id = player_to_team_histories.team_history_id
WHERE
    player_to_team_histories.id = 574651

这会提取我们感兴趣的大学球队的 team_history_id，这使我们能够获得我们选择的球员的队友(由 player_to_team_history.id = 574651 标识)，因为所有队友都会有相同的 team_history_id。

队友

SELECT 
    player_id
FROM
    player_to_team_histories
WHERE
    player_to_team_histories.not_valid IS NULL AND team_history_id = (SELECT 
        team_history_id
    FROM
        player_to_team_histories
            INNER JOIN
        team_histories ON team_histories.id = player_to_team_histories.team_history_id
    WHERE
        player_to_team_histories.id = 574651)

我们使用该 team_history_id 来获取所选玩家的所有队友。然后，我们使用玩家找到他们的高中。

高中队

SELECT 
    Accounts.id, Accounts.Name, COUNT(Accounts.Name) AS count
FROM
    player_to_team_histories
        INNER JOIN
    team_histories ON team_histories.id = player_to_team_histories.team_history_id
        INNER JOIN
    teams ON teams.id = team_histories.team_id
        INNER JOIN
    accounts ON accounts.id = teams.account_id
WHERE
    accounts.AccountTypeId = 1 AND player_id IN (SELECT 
        player_id
    FROM
        player_to_team_histories
    WHERE
        player_to_team_histories.not_valid IS NULL AND team_history_id = (SELECT 
            team_history_id
        FROM
            player_to_team_histories
                INNER JOIN
            team_histories ON team_histories.id = player_to_team_histories.team_history_id
        WHERE
            player_to_team_histories.id = 574651))
GROUP BY Accounts.Name
ORDER BY count DESC

通过抓取我们感兴趣的所有球员的与高中相关的player_to_team_history(accounts.AccountTypeId = 1)，我们可以找出队友效力于哪些高中at，按 accounts.id 对它们进行分组，然后按每个组的计数进行排序，从而为我们提供一个排序列表，其中列出了哪些高中的球员在大学花名册上最多。

排名

SELECT WrappedQuery.rank FROM
(SELECT 
    @rownum := @rownum+1 AS rank, q.Name, q.id
FROM    
    (SELECT @rownum := 0) counter, 
    (SELECT 
        Accounts.id, Accounts.Name, COUNT(Accounts.Name) AS count
    FROM
        player_to_team_histories
    INNER JOIN team_histories ON team_histories.id = player_to_team_histories.team_history_id
    INNER JOIN teams ON teams.id = team_histories.team_id
    INNER JOIN accounts ON accounts.id = teams.account_id
    WHERE
        accounts.AccountTypeId = 1 AND player_id IN (SELECT 
            player_id
        FROM
            player_to_team_histories
        WHERE
            player_to_team_histories.not_valid IS NULL AND team_history_id = (SELECT 
                team_history_id
            FROM
                player_to_team_histories
            INNER JOIN team_histories ON team_histories.id = player_to_team_histories.team_history_id
            WHERE
                player_to_team_histories.id = 574651))
    GROUP BY Accounts.Name
    ORDER BY count DESC) q) WrappedQuery
WHERE WrappedQuery.id = 7661

我们通过对排名的每一行进行编号并获取我们感兴趣的行来结束。在这种情况下，我们对 AccountId 为 7661 的高中感兴趣。这是所选玩家就读的高中，而这将告诉我们，在所有为当前大学名单提供球员的高中中，我们选择的球员高中排名。

如何在 Rails 中执行此操作

这就是我迷路的地方。我将如何进行这些嵌套连接/子查询和结果排名？

我完全理解这可能是解决此问题的糟糕方法。将其分解为多个查询并在 Rails 中将所有内容拼接在一起会更好吗？

除了执行 select_by_sql 之外，还有什么地方我可以使用 Rails 来简化这件事吗？

版本

Rails 3.2.1
Ruby 1.9.2 

Answer 1

这可以使用 AREL 完成，但需要大量挖掘。但是我过去用来使子查询更容易的是使用 squeel

“Squeel 使位于 ActiveRecord 之下的 ARel 功能更易于访问，让您可以使用更少的字符串和更多的 Ruby 编写 ActiveRecord 查询。”