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

问题描述

在带有mysql的Rails 3中，假设我有两个模型，Customers和Purchases，显然购买的是owns_to客户.我想找到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中的构建块(位置，拥有，分组等)结合起来，以实现此目的，这在(psudo-code)的代码上是这样的:

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屋！