通过ActiveRecord中的外键排序:是否没有联接? [英] Order by foreign key in activerecord: without a join?

问题描述

我想扩大这个问题. 通过ActiveRecord中的外键排序

I want to expand this question. order by foreign key in activerecord

我正在尝试根据一个非常大的表中的值对一组记录进行排序. 当我使用join时，它将所有其他"记录数据带入对象中.

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

现在记录包含35个字段.

Now record holds 35 fields..

有没有连接的方法吗?

这是我的想法..
通过加入，我得到这个查询

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.

当我手工编写sql时.

When I write sql by hand.

SELECT * FROM bids 
order by (select users.ranking from users where users.id = runner_id) DESC
limit 1

我认为这是一个更快的查询，基于解释"，它似乎更简单.
比速度更重要的是，第二种方法没有30个额外的字段.
如果我在范围内构建自定义选择，则如果对象也具有自定义选择(可能只有一个)，它可能会使对象上的其他搜索爆炸

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)

推荐答案

在主动记录编写中要实现的目标是

What you would like to achieve in active record writing is something along

SELECT b.* from bids b inner join users u on u.id=b.user_id order by u.ranking desc

在活动记录中，我将这样写: Bids.joins("inner join users u on bids.user_id=u.id").order("u.ranking desc")

In active record i would write such as: Bids.joins("inner join users u on bids.user_id=u.id").order("u.ranking desc")

我认为这是在不从用户模型中获取所有属性的情况下进行联接的唯一方法.

I think it's the only to make a join without fetching all attributes from the user models.

这篇关于通过ActiveRecord中的外键排序:是否没有联接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！