Rails: Find relations on relations
<p>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:</p>
<p>I have two models: Categories and Products and a CategoryProductRelation-model to tie them together.</p>
<p>Category-model:</p>
<pre><code>has_many :category_product_relations
has_many :products, :through =&gt; :category_product_relations
</code></pre>
<p>CategoryProductRelation-model:</p>
<pre><code>has_many :products
has_many :categories
</code></pre>
<p>Category typically has some 1000 products.</p>
<h2>The problem</h2>
<p>Very often I need to do searches like &quot;All products in the Food type of categories&quot; i.e. Categories: 'Fruits' (1), 'Cereal' (2), 'Meat'(3).</p>
<p>Basically, what I would <em>like</em> to do is something like <code>Category.where(:id =&gt; [1,2,3]).products</code> which obviously doesn't work.</p>
<h2>My (slow) solution</h2>
<p>What I often do is something like this:</p>
<pre><code>products = Product.where(:id =&gt; CategoryProductRelation.where(:category_id =&gt; [1,2,3]).pluck(:product_id))
</code></pre>
<p>which works, mostly fine as well. However, this is very slow if the amount of products is large resulting in 400ms queries!</p>
<p>There <strong>has</strong> 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 <strong>fast</strong>!</p>
<p>Lastly, iterating through Category like:</p>
<pre><code>Category.where(:id =&gt; [1,2,3]).each do |category|
category.products 
#...and so on
end
</code></pre>
<p>seems to be equally slow.</p>
<p>Is there a magic bullet to this common problem?</p>