将SQL查询转换为ActiveRecord关系

本文介绍了将SQL查询转换为ActiveRecord关系的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

如何将以下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")