以关联计数为条件的 Rails 3 查询 [英] Rails 3 query on condition of an association's count

问题描述

在带有 mysql 的 Rails 3 中，假设我有两个模型，Customers 和 Purchases，显然是购买属于客户的.我想找到所有有 2 个或更多订单的客户.我可以简单地说:

In Rails 3 with mysql, suppose I have two models, Customers and Purchases, obviously purchase belongs_to customer. I want to find all the customers with 2 orders or more. I can simply say:

Customer.includes(:purchases).all.select{|c| c.purchases.count > 2}

但实际上，上面的行查询 Customer.all 和 Purchase.all 的大小，然后在 ruby​​ 中进行选择"类型处理.在大型数据库中，我更愿意避免在 ruby​​ 中进行所有这些选择"计算，而让 mysql 进行处理并且只给我合格客户的列表.这既快得多(因为 mysql 更适合这样做)并且显着减少了数据库的带宽.

Effectively though, the line above makes query on the magnitude of Customer.all and Purchase.all, then does the "select" type processing in ruby. In a large database, I would much prefer to avoid doing all this "select" calculation in ruby, and have mysql do the processing and only give me the list of qualified customers. That is both much faster (since mysql is more tuned to do this) and significantly reduces bandwidth from the database.

不幸的是，我无法用 rails 中的构建块(where、have、group 等)来调用代码来实现这一点，(伪代码):

Unfortunately I am unable to conjure up the code with the building blocks in rails(where, having, group, etc) to make this happen, something on the lines of (psudo-code):

Customer.joins(:purchases).where("count(purchases) > 2").all

我将满足于直接的 MySql 解决方案，尽管我更喜欢在优雅的 rails 框架中解决这个问题.

I will settle for straight MySql solution, though I much prefer to figure this out in the elegant framework of rails.

推荐答案

在这一点上，关于这个东西的文档相当稀少.如果您要进行更多与此类似的查询，我会考虑使用 Metawhere.使用 Metawhere，您可以执行此操作(或类似的操作，不确定语法是否完全正确):

The documentation on this stuff is fairly sparse at this point. I'd look into using Metawhere if you'll be doing any more queries that are similar to this. Using Metawhere, you can do this (or something similar, not sure if the syntax is exactly correct):

Customer.includes(:purchases).where(:purchases => {:count.gte => 2})

这样做的好处是 MetaWhere 仍然使用 ActiveRecord 和 arel 来执行查询，因此它可以与新的"rails 3 查询方式一起使用.

The beauty of this is that MetaWhere still uses ActiveRecord and arel to perform the query, so it works with the 'new' rails 3 way of doing queries.

此外，您可能不想在最后调用 .all，因为这会导致查询 ping 数据库.相反，您希望使用延迟加载，并且在您实际需要数据(在视图中，或处理实际数据的其他方法中)之前不要访问数据库.

Additionally, you probably don't want to call .all on the end as this will cause the query to ping the database. Instead, you want to use lazy loading and not hit the db until you actually require the data (in the view, or some other method that is processing actual data.)

这篇关于以关联计数为条件的 Rails 3 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！