ruby-on-rails - 尝试通过关系显示所有项目时出现 Postgres 错误

Question

这适用于 SQLite3，但不适用于 PostgreSQL。

我得到的错误是 PG::InvalidColumnReference: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

我正在尝试显示 group 中的所有 exercises，代码为:current_user.group.exercises

这是关系

一组有_很多锻炼，一个锻炼有_很多锻炼

在我的组模型中，我有 has_many :exercises, through: :workouts

有什么想法吗？

编辑 1:

这是 Rails 正在生成的 SQL:

SELECT DISTINCT "exercises".* 
FROM "exercises" 
  INNER JOIN "workout_exercises" ON "exercises"."id" = "workout_exercises"."exercise_id" 
  INNER JOIN "workouts" ON "workout_exercises"."workout_id" = "workouts"."id" 
  INNER JOIN "groups_workouts" ON "workouts"."id" = "groups_workouts"."workout_id" 
WHERE "groups_workouts"."group_id" = 2 
ORDER BY exercise_order, workout_order

这里是错误:

PG::InvalidColumnReference: ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 1: ..." WHERE "groups_workouts"."group_id" = 2 ORDER BY exercise_o...
                                                             ^
: SELECT DISTINCT "exercises".* FROM "exercises" INNER JOIN "workout_exercises" ON "exercises"."id" = "workout_exercises"."exercise_id" INNER JOIN "workouts" ON "workout_exercises"."workout_id" = "workouts"."id" INNER JOIN "groups_workouts" ON "workouts"."id" = "groups_workouts"."workout_id" WHERE "groups_workouts"."group_id" = 2 ORDER BY exercise_order, workout_order

Answer 1

所以这是一个uniq约束异常。在模型上我有 has_many :exercises, through: :workouts, uniq: true Postgres 不喜欢。

为了修复错误，我将 uniq 约束从模型移到了实际查询中。所以在这种情况下，我只是做了 current_user.group.exercises.uniq

这只能解决我的问题。在某些情况下，我希望在模型级别有​​一个 uniq 约束，但我还没有找到一种方法来做到这一点。