CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3activerecord
Fellow Stranger
Fellow Stranger

Reputation: 34013

Selecting records where relation is not empty

I have a one-to-many relationship between Product and 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

Upvotes: 9

Views: 8536

Answers (4)

Veraticus
Veraticus

Reputation: 16064

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

Upvotes: 4

Fellow Stranger
Fellow Stranger

Reputation: 34013

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

Upvotes: 5

elc
elc

Reputation: 1952

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.

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

Upvotes: 3

Constant Meiring
Constant Meiring

Reputation: 3325

A slightly more readable solution:

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

Upvotes: 0

Related Questions