I want to expand this question. order by foreign key in activerecord
I'm trying to order a set of records based on a value in a really large table. When I use join, it brings all the "other" records data into the objects.. As join should..
#table users 30+ columns #table bids 5 columns record = Bid.find(:all,:joins=>:users, :order=>'users.ranking DESC' ).first
Now record holds 35 fields..
Is there a way to do this without the join?
Here's my thinking..
With the join I get this query
SELECT * FROM "bids" left join users on runner_id = users.id ORDER BY ranking LIMIT 1
Now I can add a select to the code so I don't get the full user table, but putting a select in a scope is dangerous IMHO.
When I write sql by hand.
SELECT * FROM bids order by (select users.ranking from users where users.id = runner_id) DESC limit 1
I believe this is a faster query, based on the "explain" it seems simpler.
More important than speed though is that the second method doesn't have the 30 extra fields.
If I build in a custom select inside the scope, it could explode other searches on the object if they too have custom selects (there can be only one)