title: Complex query, use :includes and :joins at the same time?
body: <p>(Using Rails 3.1.3)</p>
  
  <p>I have an app that manages products. I import the products from several resellers and they all name their categories different. Because of this I have resellercategories that are mapped to my own subcategories.</p>
  
  <p>Categories</p>
  
  <p>Subcategories (belongs_to Category)</p>
  
  <p>Resellercategories (belongs_to Subcategory)</p>
  
  <p>Products (belongs_to Resellercategory)</p>
  
  <p>You can see the models and how the relations are defined here:
  <a href="http://snipt.net/Linuus/category-and-subcategory?key=38ba590408ac4233927a06046eeca30d" rel="nofollow">http://snipt.net/Linuus/category-and-subcategory?key=38ba590408ac4233927a06046eeca30d</a></p>
  
  <p>On my site I want to display the categories and their subcategories, easy.</p>
  
  <p>If a user filters the products for, say, only 'female' products I want to filter also the categories and subcategories so that only categories and subcategories that have 'female' products are displayed. The gender is stored in the products.</p>
  
  <p>So, how can I do this?</p>
  
  <p>I tried to create a query like this:
  <a href="http://snipt.net/Linuus/categories-1/?key=2d5d54fd573f0afe60eaa3c47a23fd4d" rel="nofollow">http://snipt.net/Linuus/categories-1/?key=2d5d54fd573f0afe60eaa3c47a23fd4d</a>
  which (I think) filters the correct Categories. However, when I do something like:</p>
  
  <pre><code>@menu_categories.each do |c|
      c.subcategories.each do |sc|
          # do something...
      end
  end
  </code></pre>
  
  <p>It still queries all the subcategories whether or not they have female products. So, I got a suggestion over at the Ruby on Rails Google Group to eagerly load the :subcategories using .includes(). So, something like this:</p>
  
  <pre><code>Category.includes(:subcategories)
          .joins("INNER JOIN resellercategories AS r ON subcategories.id = r.subcategory_id")
          .joins("INNER JOIN products AS p ON r.id = p.resellercategory_id")
          .group("categories.id")
          .order("categories.name ASC")
          .where("p.gender = 'unisex' OR p.gender = 'female'")
          .where("subcategories.id &gt; 0") # Dummy to trigger eager loading
  </code></pre>
  
  <p>However, when mixing .includes() and .joins() the includes seems to fail to eager load anything. Thus throwing the error below:</p>
  
  <pre><code>ActiveRecord::StatementInvalid: SQLite3::SQLException: no such column: subcategories.id:
  SELECT "categories".* FROM "categories" 
  INNER JOIN resellercategories AS r ON subcategories.id = r.subcategory_id 
  INNER JOIN products AS p ON r.id = p.resellercategory_id 
  WHERE (p.gender = 'unisex' OR p.gender = 'female') 
  GROUP BY categories.id 
  ORDER BY categories.name ASC
  </code></pre>
  
  <p>Is this behavior expected? Is it a bug? 
  Am I trying to do this the right way or is there a better way to do it?</p>
  
  <p>Any help is very appreciated.</p>
  
  <p>(The discussion on RoR Google Group: <a href="https://groups.google.com/forum/?pli=1#!topic/rubyonrails-talk/UkCF7jbehHk" rel="nofollow">https://groups.google.com/forum/?pli=1#!topic/rubyonrails-talk/UkCF7jbehHk</a>)</p>
  
  <p><strong>Solution:</strong>
  Ok, so the solution is to use eager_load() instead of includes(). I also had to remove group()
  This seems to work for me:</p>
  
  <pre><code> Category.eager_load(:subcategories)
           .joins("INNER JOIN resellercategories AS r ON subcategories.id = r.subcategory_id")
           .joins("INNER JOIN products AS p ON r.id = p.resellercategory_id")
           .order("categories.name ASC")
           .where("p.gender = 'unisex' OR p.gender = 'female'")
  </code></pre>