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?

loganhasson · Accepted Answer · 2014-04-16 18:54:44Z

3

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")

answered Apr 16, 2014 at 18:54

loganhasson

1,1802 gold badges12 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related