Returning distinct or grouped records from a postgres DB using active record queries

Question

I'm stuck on an AREL call I'm trying to write for a Rails 4.1 application with a postgres database.

My app has activerecord classes for User and League (users table and a leagues table) and they're joined via a has_many relationship with a user_leagues table.

I'm trying to write an instance method on League that returns users from this league ordered by the end_score column in the user_leagues table. I could do it with Ruby, but it seems like it should be doable with pure AREL.

In pseudocode: select * from users ...inner join user_leagues... group_by user_id order_by end_score

My latest attempt:

  def ranked_users
    User.joins(:user_leagues).
    where("user_leagues.league_id = ?", self.id).
    group("user_leagues.user_id").order("user_leagues.end_score DESC")
  end

I've tried different variations with group and distinct, but all of them return some variation of this error:

PG::GroupingError: ERROR:  column "users.id" must appear in the GROUP BY clause or be used in an aggregate function

Anyone know how to do this?

Answer 1

Try this one...I think it should work:

User.joins(:user_leagues).
  where("user_leagues.league_id = ?", self.id).
  order("user_leagues.end_score DESC")