Postgres error when trying to display all items through a relationship

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

Solution

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.