选择关系不为空的记录

I have a one-to-many relationship between Product and ProductCategory.

我在Product和ProductCategory之间有一对多的关系。

How do I query all productcategories that have at least one product associated to them?

如何查询至少有一个与之关联的产品的所有产品类别?

class Product < ActiveRecord::Base
  belongs_to  :product_category
end

class ProductCategory < ActiveRecord::Base
  has_many :products
end

4 个解决方案

#1

ProductCategory.all(
  :joins => :products, 
  :select => "product_categories.*, count(products.id) as prod_count",
  :group => "product_categories.id"
)

I found out how to solve this thanks to the great Ryan Bates on this screencast: http://railscasts.com/episodes/181-include-vs-joins

我找到了如何解决这个问题,感谢伟大的Ryan Bates在这个截屏视频:http://railscasts.com/episodes/181-include-vs-joins

#2

ProductCategory.includes(:products).where('products.id is not null').all

ProductCategory.includes(:products).where('products.id not null')。all

#3

Joins makes an inner join, so the where clause in some of the other answers is superfluous. Grouping by products.id as some others do will repeat the category when the category has multiple products, grouping by the ProductCategory will eliminate dups.

连接是一个内连接,所以其他一些答案中的where子句是多余的。按产品分类。如同其他人一样,当类别包含多个产品时,将重复该类别,按ProductCategory分组将消除重复。

ProductCategory.joins(:products).group('product_categories.id')

#4

A slightly more readable solution:

更易读的解决方案:

ProductCategory.joins(:products).where('product_categories.id is not null').group('products.id')

#1

ProductCategory.all(
  :joins => :products, 
  :select => "product_categories.*, count(products.id) as prod_count",
  :group => "product_categories.id"
)