sql - 将 SQL 查询转化为 ActiveRecord Relation

Question

如何将以下 SQL 查询转换为 ActiveRecord 关系，以便我可以使用范围对其进行扩展？

WITH joined_table AS (
    SELECT workout_sets.weight AS weight, 
        workouts.user_id AS user_id, 
        workouts.id AS workout_id, 
        workout_sets.id AS workout_set_id,
        workout_exercises.exercise_id AS exercise_id
    FROM workouts 
    INNER JOIN workout_exercises ON workout_exercises.workout_id = workouts.id 
    INNER JOIN workout_sets ON workout_sets.workout_exercise_id = workout_exercises.id       
    ORDER BY workout_sets.weight DESC
    ),

sub_query AS (
    SELECT p.user_id, MAX(weight) as weight
        FROM joined_table p
            GROUP BY p.user_id
),

result_set AS (
    SELECT MAX(x.workout_id) AS workout_id, x.user_id, x.weight, x.workout_set_id, x.exercise_id
    FROM joined_table x
    JOIN sub_query y 
    ON y.user_id = x.user_id AND y.weight = x.weight
    GROUP BY x.user_id, x.weight, x.workout_set_id, x.exercise_id
    ORDER BY x.weight DESC)

SELECT workouts.*, result_set.weight, result_set.workout_set_id, result_set.exercise_id
FROM workouts, result_set
WHERE workouts.id = result_set.workout_id 

这是我必须尝试直接使用 ARel 的东西吗？

我尝试将其分解为作用域/子查询，但是子查询上的选择最终出现在封闭查询中，因此抛出 PostgreSql 错误，因为该列未在封闭语句中的 GROUP BY 或 ORDER BY 中指定。

更新:
您认为它是 PostgreSql 的假设是正确的。我尝试了您的查询，但它抛出了 PG::Error: ERROR: column "rownum" does not exist ，对于直接查询和 ActiveRecord 等价。

但是，当我将查询包装在单独的查询中时，它可以工作。我假设 ROW_NUMBER() 在选择被投影到数据集之后才被创建。因此，以下查询有效:

SELECT workouts.*, t.weight, t.workout_set_id, t.exercise_id, t.row_num
FROM workouts,
(SELECT workouts.id as workout_id, workout_sets.weight as weight,
                workout_sets.id AS workout_set_id,
                   workout_exercises.id AS exercise_id,
                   ROW_NUMBER() OVER ( 
            PARTITION BY workouts.user_id 
            ORDER BY workout_sets.weight DESC, workouts.id DESC ) row_num
     FROM workouts
     JOIN workout_exercises ON workout_exercises.workout_id = workouts.id 
     JOIN workout_sets ON workout_sets.workout_exercise_id = workout_exercises.id) as t
WHERE workouts.id = t.workout_id AND t.row_num = 1

我已经设法按摩到以下内容:

  selected_fields = <<-SELECT
    workouts.id AS workout_id, 
    workout_sets.weight AS weight,
    workout_sets.id AS workout_set_id,
    workout_exercises.id AS exercise_id,
    ROW_NUMBER() OVER (
       PARTITION BY workouts.user_id 
       ORDER BY workout_sets.weight DESC, workouts.id DESC) as row_num
  SELECT

  Workout.joins(", (#{Workout.joins(:workout_exercises => :workout_sets).select(selected_fields).to_sql}) as t").select("workouts.*, t.*").where("workouts.id = t.workout_id AND t.row_num = 1").order("t.weight DESC")

但正如你所看到的，这是非常糟糕的，并且是一种大量的代码气味。关于如何重构它的任何想法？

Answer 1

您显然正在尝试获取与每个用户的最高权重相匹配的最新锻炼(最高 ID)详细信息。看来您正在使用 PostgreSQL(MySQL 没有 CTE)，如果我错了，请纠正我。

如果是这样，您可以使用窗口函数并将查询简化为:

SELECT * FROM (
  SELECT workouts.*, workout_sets.weight,
                     workout_sets.id AS workout_set_id,
                     workout_exercises.id AS exercise_id,
                     ROW_NUMBER() OVER (
                         PARTITION BY workouts.user_id 
                         ORDER BY workout_sets.weight DESC, workouts.id DESC ) as rowNum
  FROM workouts
  JOIN workout_exercises ON workout_exercises.workout_id = workouts.id 
  JOIN workout_sets ON workout_sets.workout_exercise_id = workout_exercises.id
) t
WHERE rowNum = 1

在 ActiveRecord 中可以写成:

selected_fields = <<-SELECT
  workouts.*, 
  workout_sets.weight,
  workout_sets.id AS workout_set_id,
  workout_exercises.id AS exercise_id,
  ROW_NUMBER() OVER (
     PARTITION BY workouts.user_id 
     ORDER BY workout_sets.weight DESC, workouts.id DESC) as rowNum
SELECT

subquery = Workout.joins(:workout_exercises => :workout_sets).
                   select(selected_fields).to_sql
Workout.select("*").from(Arel.sql("(#{subquery}) as t"))
       .where("rowNum = 1")