CODEWITHSUNDEEP
ruby-on-railsruby
Christoffer
Christoffer

Reputation: 2411

Rails: Find relations on relations

I can't believe I haven't come up with a good solution to this common problem after 5 years of Rails-programming. Also, I assume there are 100's of answers to this particular question but I don't know the definitions (relations? associations? etc) to search it well. So here we go:

I have two models: Categories and Products and a CategoryProductRelation-model to tie them together.

Category-model:

has_many :category_product_relations
has_many :products, :through => :category_product_relations

CategoryProductRelation-model:

has_many :products
has_many :categories

Category typically has some 1000 products.

The problem

Very often I need to do searches like "All products in the Food type of categories" i.e. Categories: 'Fruits' (1), 'Cereal' (2), 'Meat'(3).

Basically, what I would like to do is something like Category.where(:id => [1,2,3]).products which obviously doesn't work.

My (slow) solution

What I often do is something like this:

products = Product.where(:id => CategoryProductRelation.where(:category_id => [1,2,3]).pluck(:product_id))

which works, mostly fine as well. However, this is very slow if the amount of products is large resulting in 400ms queries!

There has to be a smarter way, right? This feels like Rails 101 but I can't figure it out in a way that is simple and fast!

Lastly, iterating through Category like:

Category.where(:id => [1,2,3]).each do |category|
category.products 
#...and so on
end

seems to be equally slow.

Is there a magic bullet to this common problem?

Upvotes: 1

Views: 93

Answers (4)

everyman
everyman

Reputation: 3407

What about merge? 

Product.joins(:categories).merge(Category.where(:id => [1,2,3])).pluck(:id)

http://apidock.com/rails/ActiveRecord/SpawnMethods/merge

Merges in the conditions from other, if other is an ActiveRecord::Relation

Upvotes: 1

Meier
Meier

Reputation: 3880

The magic word is database join. To use it, you need to think a little in the opposite direction. You ask for products with conditions on a joined table.

http://guides.rubyonrails.org/active_record_querying.html#joining-tables

specificly look for: "12.3. 12.3 Specifying Conditions on the Joined Tables"

So it is something like

Products.joins(:category_product_relations).
  where( category_product_relations: {category_id: [1,2,3]} )

Upvotes: 1

Harry Bomrah
Harry Bomrah

Reputation: 1668

I am not sure if this works in your case. I use mongoid so i do something like this.

Product.where(:category_id.in => [1,2,3])

This gives me all the products with the categories whose ids are 1,2,3.

For Product model

belongs_to :category

For Category model

has_many :products

I hope you get an idea.

For two queries you can do something like this.

category_product_relations_ids = CategoryProductRelation.where(:category_ids => [1,2,3]).pluck(:id)
Product.where(:category_product_relation_id => category_product_relations_ids)

Upvotes: 1

Amadan
Amadan

Reputation: 198304

includes should help. Didn't test, but something like:

Category.includes(:products).where(id: [1, 2, 3]).flat_map(&:products)

Upvotes: 1

Related Questions