将SQL查询转换为ActiveRecord关系

凡庆象

如何将以下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 =  :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")
但是，正如您所知道的那样，这非常骇人，并且有大量的代码味道。关于如何重构的任何想法？

解决方案:


            您显然正在尝试获取与每个用户的最高体重相匹配的最新锻炼（最高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 =  :workout_sets).
               select(selected_fields).to_sql
Workout.select("*").from(Arel.sql("(#{subquery}) as t"))
   .where("rowNum = 1")

将SQL查询转换为ActiveRecord关系

凡庆象 LV1