1

This works on SQLite3, but not on PostgreSQL.

The error I'm getting is PG::InvalidColumnReference: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

I'm trying to display all exercises that are in a group with the code: current_user.group.exercises

Here is the relationship

A group has_many workouts, and a workout has_many exercises

In my Group model I have has_many :exercises, through: :workouts

Any ideas?

EDIT 1:

Here is the SQL rails is generating:

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

And here is the error:

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
Chris Travers
  • 25,424
  • 6
  • 65
  • 182
Arel
  • 3,888
  • 6
  • 37
  • 91

2 Answers2

3

So this is a uniq constraint exception. On the model I had has_many :exercises, through: :workouts, uniq: true which Postgres didn't like.

To fix the error, I moved the uniq constraint from the model to the actual query. So in this situation, I just did current_user.group.exercises.uniq

This only sort of solves my problem. There are situations where I would want to have a uniq constraint at the model level, but I haven't been able to find a way to do that yet.

Arel
  • 3,888
  • 6
  • 37
  • 91
1

Each SQL variant has slightly different rules as to what expressions it accepts. For example, see Simulating MySQL's ORDER BY FIELD() in Postgresql and related links for information on this issue. If you give the specifics of the SQL you're generating, you can probably get more specific advice.

Community
  • 1
  • 1
Peter Alfvin
  • 28,599
  • 8
  • 68
  • 106
  • Thanks, I just edited my question to include the SQL that rails is generating, and the PSQL error. I'm also taking a look at the link now. – Arel Nov 19 '13 at 02:03