lundi 20 avril 2015

Order by foreign key in activerecord: without a join?

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)

Aucun commentaire:

Enregistrer un commentaire