0

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

Jakub Kuchar
  • 1,665
  • 2
  • 23
  • 39

1 Answers1

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.

David Aldridge
  • 51,479
  • 8
  • 68
  • 96
  • 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. – Jakub Kuchar Jul 06 '13 at 07:06
  • 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? – David Aldridge Jul 06 '13 at 08:08
  • 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.. – Jakub Kuchar Jul 07 '13 at 05:49
  • 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. – David Aldridge Jul 07 '13 at 08:31
  • 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. – Jakub Kuchar Jul 07 '13 at 10:54