Rails - 跨多个关系有效地提取和计算数据 [英] Rails - Efficiently pull and calculate data across several relationships

问题描述

尝试使用 Rails 2.3 和 MySQL 以最有效的方式获取某些报告的数据.

Trying to get data in the most efficient way possible for some reports, using Rails 2.3 and MySQL.

我们的应用有 Users、Deals 和 PurchasedDeals.关系如下所示:

Our app has Users, and Deals, and PurchasedDeals. Relationships look like this:

class User
  has_many :purchased_deals
  has_many :deals, :through => :purchased_deals
end

class Deal
  has_many :purchased_deals
  has_many :users, :through => :purchased_deals
end

class PurchasedDeal
  belongs_to :deal
  belongs_to :user
end

对于我正在运行的报告，我需要获取已购买的所有用户(即至少有一个 PurchasedDeal)，然后是他们购买的所有交易的总和(价格附在 Deal，而不是 PurchasedDeal).

For the report I'm running, I need to get all users that have made a purchase (i.e. have at least one PurchasedDeal), and then the sum total of all the deals they have bought (price is attached to the Deal, not the PurchasedDeal).

当然，我可以从所有用户的列表开始，包括交易和购买的交易.我试过了，查询量很大(30,000 个用户，给予或接受，3,000 笔交易，100,000 多笔购买交易).

Certainly I could start with a list of all users, including both deals and purchased deals. I've tried that, and the query is massive (30,000 users, give or take, 3,000 deals, 100,000+ purchased deals).

我可以从用户开始，然后做一个 .each 并找到那些有购买交易的人，把他们分成他们自己的组，然后遍历每个那些以获得总数购买数量，但这是相当数量的查询.

I could start with users, then do a .each and find the ones that have a purchased deal, split them out into their own group, and then iterate over each of those to get the total purchased amount, but that is a fair amount of queries.

目前，这两种方法都需要很长时间，以至于请求超时.获取我需要的数据的最有效方法是什么?向表中添加列是完全可以接受的解决方案，顺便说一句.我拥有完整的数据库访问权限，可以执行我需要的操作.

Currently, both of these methods take so long that the requests are timing out. What would the most efficient way be to get the data I need? Adding columns to tables is a totally acceptable solution, btw. I have full database access to do what I need.

谢谢！

推荐答案

要获取多次购买的用户 ID 列表，您可以执行以下操作，这将只访问一张表:

To get a list of user IDs with more than one purchase, you can do the following, which will access just one table:

user_ids = PurchasedDeal.count(:group => :user_id, :having => 'count_all > 0').keys

随后，您可以通过以下方式获取所有这些用户:

Subsequently, you can fetch all these users with:

users = User.find user_ids

<小时>

可以通过计数器缓存加快速度.在您的用户模型中，添加选项 :counter_cache =>对于已购买的交易，对 has_many 关联是真实的.您需要在 users 表上添加一个额外的整数列并进行初始化，在迁移中可能如下所示:

---

Things can be sped up with a counter cache. In your user model, add the option :counter_cache => true to the has_many association for purchased deals. You'll need an extra integer column on your users table and initialize, which might look as follows in a migration:

add_column :users, :purchased_deals_count, :integer, :null => false, :default => 0
User.each { |u| User.reset_counters u, :purchased_deals }

一旦解决了这个问题，事情就变得简单多了:

Once that's out of the way, it becomes a lot simpler:

users = User.all :conditions => 'purchased_deals_count > 0'

Rails 将通过大多数标准操作为您保持该列的最新状态.

Rails will keep the column up-to-date for you, with most standard operations.

要获得总价将始终涉及连接.或者，您可以构建交易价格的哈希值并在 Ruby 中进行繁琐的处理.我不是 SQL 专家，但您可以通过使用 PurchasedDeal 存储价格来摆脱连接.否则，这里是如何通过连接来实现的:

To get the total price will always involve a join. Or you can build a hash of deal prices and do the tedious processing in Ruby. I'm no SQL expert, but you can potentially get rid of the join by storing the price with the PurchasedDeal. Otherwise, here's how to do it with a join:

user_id_to_price = PurchasedDeal.sum 'deal.price', :include => :deal, :group => :user_id

您可以通过添加类似 :conditions => 之类的内容来过滤您想要的用户.['user_id IN (?)', users].(其中 users 可以是 ID 列表，也可以是 User 对象.)

You could filter that on just the users you want by adding something like :conditions => ['user_id IN (?)', users]. (Where users can be a list of IDs, but also User objects.)

这篇关于Rails - 跨多个关系有效地提取和计算数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！