Вопрос по ruby-on-rails-4, sql, active-relation, activerecord, ruby-on-rails – Превратить SQL-запрос в ActiveRecord Relation

10

Как я могу превратить следующий 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, потому что столбец не 't указано в GROUP BYs или ORDER BYs во вложении оператора.

Обновить: Вы правы в своем предположении, что этоs 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 = 
Можете ли вы проверить, используете ли вы PostgreSQL? PinnyM
Этот запрос использует CTE 's, которые напрямую не поддерживаются ActiveRecord или Arel. Если вы реорганизуете это, чтобы избежать CTE 's, вы можете сделать это с любым из них. PinnyM
Мне тоже нужна эта функция! Pavel Evstigneev

Ваш Ответ

1   ответ
8

очевидно, пытаетесь получить последние данные о тренировках (с наибольшим идентификатором), которые соответствуют наибольшему весу для каждого пользователя. Также кажется, что вы используете PostgreSQL (MySQL нене иметь CTE 's) поправь меня если яЯ не прав в этом.

Если это так, вы можете использовать функции управления окнами и упростить ваш запрос до:

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")
Пинни, яМы обновили исходный вопрос с дополнительной информацией. Спасибо за помощь. MunkiPhD
@MunkiPhD: вы правы насчет необходимости подзапроса - обновляется с использованием Arelfrom а такжеsql чтобы обойти беспорядок. PinnyM

Похожие вопросы