Rails 3 Order Records By Grand-child Count

Question

I'm trying to do some fairly complicated record sorting that I was having a bit of trouble with. I have three models:

    class User < ActiveRecord::Base

    has_many :registers
    has_many :results, :through => :registers

    #Find all the Users that exist as registrants for a tournament
    scope :with_tournament_entrees, :include => :registers, :conditions => "registers.id IS NOT NULL"

end

Register

class Register < ActiveRecord::Base
  belongs_to :user
  has_many :results
end

Result

class Result < ActiveRecord::Base
  belongs_to :register 
end

Now on a Tournament result page I list all users by their total wins (wins is calculated through the results table). First thing first I find all users who have entered a tournament with the query:

User.with_tournament_entrees

With this I can simply loop through the returned users and query each individual record with the following to retrieve each users "Total Wins":

user.results.where("win = true").count()

However I would also like to take this a step further and order all of the users by their "Total Wins", and this is the best I could come up with:

User.with_tournament_entrees.select('SELECT *, 
                                    (SELECT count(*)  
                                     FROM results 
                                     INNER JOIN "registers" 
                                     ON "results"."register_id" = "registers"."id" 
                                     WHERE "registers"."user_id" = "users.id" 
                                     AND (win = true)
                                    ) AS total_wins 
                                     FROM users ORDER BY total_wins DESC')

I think it's close, but it doesn't actually order by the total_wins in descending order as I instruct it to. I'm using a PostgreSQL database.

Edit:

There's actually three selects taking place, the first occurs on User.with_tournament_entries which just performs a quick filter on the User table. If I ignore that and try

SELECT *, (SELECT count(*) FROM results INNER JOIN "registers" ON "results"."register_id" = "registers"."id" WHERE "registers"."user_id" = "users.id" AND (win = true)) AS total_wins FROM users ORDER BY total_wins DESC;

it fails in both PSQL and the ERB console. I get the error message:

PGError: ERROR: column "users.id" does not exist

I think this happens because the inner-select occurs before the outer-select so it doesn't have access to the user id before hand. Not sure how to give it access to all user ids before than inner select occurs but this isn't an issue when I do User.with_tournament_entires followed by the query.

I wouldn't even know where to begin writing this ActiveRecord query as raw sql readable by psql. I tried using the to_sql method but that produced garbage sql as usual. I'm assuming the results would be the same though unless your suggesting that there's some rails magic in place. As far as the order is concerned, well suffice to say that `ORDER BY total_wins DESC` does absolutely nothing. I can change `DESC` to `ASC` and get the exact same results. With the query that I have aside do you know of a way to order the User records by the **total wins** I calculate on the grandchild records? — Noz, Jun 20 '12 at 22:52
You have raw sql already; execute the `SELECT *, (SELECT count(*) FROM results...` and see what happens. — Dondi Michael Stroma, Jun 20 '12 at 23:00
I edited my answer please have a look. I'm thinking that the result set it returns is the encompassing `User.with_tournament_entrees` which doesn't have any ordering and calling that alone gives me the same results I'm currently getting (no ordering) when what I actually want is the sub-selects... thoughts? — Noz, Jun 20 '12 at 23:18
`"users.id"` - it is quoted wrong. Should be `"users"."id"`, or, just `users.id` (you only need to quote it if you have a table/column name that conflicts with a postgres keyword). — Dondi Michael Stroma, Jun 20 '12 at 23:30
Doh! That SQL on it's own works now and sorts properly. I should be able to simply integrate my scope with that SQL so that I'm not performing two selects on the User table. If you post your comment as an answer I'll accept it. :) — Noz, Jun 20 '12 at 23:41

score 2 · Accepted Answer · answered Jun 20 '12 at 23:53

In your SQL, "users.id" is quoted wrong -- it's telling Postgres to look for a column named, literally, "users.id".

It should be "users"."id", or, just users.id (you only need to quote it if you have a table/column name that conflicts with a postgres keyword, or have punctuation or something else unusual).

Rails 3 Order Records By Grand-child Count

1 Answers1