将SQL查询转换为ActiveRecord关系 [英] Turn SQL query into ActiveRecord Relation

问题描述

如何将以下SQL查询转换为ActiveRecord关系，以便可以在范围内进行扩展？

How can I turn the following SQL query into an ActiveRecord relation so that I can expand on it with scopes?

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吗？

Is this something I would have to attempt with straight ARel?

我尝试将其分解为作用域/子查询，但是子查询中的selects最终包含在封闭的查询中，因此抛出PostgreSql错误，因为该列不是

I've tried breaking it up into scopes/subqueries, but the selects on the subqueries end up in the enclosing query, thus throwing PostgreSql errors because the column isn't specified in the GROUP BYs or ORDER BYs in the enclosing statement.

更新：
您认为它是PostgreSql是正确的。我尝试了您的查询，但是对于直接查询和ActiveRecord等效项，它都抛出 PG :: Error：错误： rownum列不存在。

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

However, when I wrap the query in a separate query, it works. I'm assuming that the ROW_NUMBER() doesn't get created until after the select is projected onto the data set. So the following query works:

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")

但是正如您所知，这非常骇人，而且庞大代码气味。关于如何重构它的任何想法吗？

But as you can tell, that's extremely hacky and is a massive code smell. Any idea as to how to refactor that?

推荐答案

您显然正在尝试获取最新的锻炼方法（ID最高）详细信息，这些信息与每个用户的最高权重相匹配。您似乎正在使用PostgreSQL（MySQL没有CTE），如果我在此方面做错了，请纠正我。

You are apparently trying to get the latest workout (highest id) details that match the highest weight for each user. It also appears that you are using PostgreSQL (MySQL doesn't have CTE's), correct me if I'm wrong on this.

如果是这样，您可以利用窗口功能并将查询简化为：

If so, you can make use of windowing functions and simplify your query to:

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中可以这样写： / p>

Which in ActiveRecord can be written as:

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")

这篇关于将SQL查询转换为ActiveRecord关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！