Rails and Postgresql how to sort with custom count? ORDER BY is ambiguous

Question

Relationship User has_many :kits

User model and named scope:

  scope :top5_users,
    joins(:kits).
    select("users.*, count(kits.id) AS kits_count").
    group("users.id, kits.id").
    order("kits_count DESC").
    limit(5)

Getting PG::Error: ERROR: ORDER BY "kits_count" is ambiguous

David Aldridge · Accepted Answer · 2013-07-06 06:49:19Z

2

That error message implies you have a column in users called kits_count, possibly maintained by a callback. If so you could more easily just:

scope :top5_users,
    order("kits_count DESC").
    limit(5)

If not ...

Support for column aliases is mixed among RDBMS vendors, and you could probably use:

order("count(kits.id) desc")

If you knew the column number you could:

scope :top5_users,
    joins(:kits).
    select("count(kits.id) AS kits_count, users.*").
    group("users.id").
    order("1 DESC").
    limit(5)

Take the kits.id out of the group by or you'll get 1 for every count.

answered Jul 6, 2013 at 6:49

David Aldridge

52.5k8 gold badges73 silver badges99 bronze badges

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

5 Comments

Jakub Kuchar Over a year ago

order("count(kits.id) desc") works. And yeah i removed kits.id from group_by, thank you for hitting me. But still not getting why cannot use the alias.

David Aldridge Over a year ago

The error suggests that there are multiple columns projected from the query that are called "kits_count" -- do you have a column of that name in the users table?

Jakub Kuchar Over a year ago

Was double checking everything User.column_names.include?("kits_count") is false, Kit.column_names.include?("kits_count") is false. And there is no other association though 3rd table or something similar..

David Aldridge Over a year ago

Well I'd just login to psql and run \d users; -- it's postgres' opinion of whether there's a column in the table called "kits_count" that is definitive. Could be that the error message is just a bit misleading though.

Jakub Kuchar Over a year ago

There is no such a column, hmm, maybe i am blind..:-) I tried to rename the alias "kits_count" to "kits_count_per_user" it worked also with ORDER, and because i am using that alias "kits_count" to print in view i was expecting to dont raise an error and see some value. But it raised error undefined kits_count. Thank you again.

Collectives™ on Stack Overflow

Rails and Postgresql how to sort with custom count? ORDER BY is ambiguous

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related