mysql - Rails 3 - Eager loading with conditions

Question

Okay, I'm thoroughly stumped on this one. I'm trying to build a menu of published web pages organized by category.

Category.rb:

belongs_to :parent, :class_name => "Category", :foreign_key => "parent_id"
has_many   :children, :class_name => "Category", :foreign_key => "parent_id"
has_many :pages, :documents, :galleries

Page.rb

belongs_to :category

The Page model also has :is_published, so I'm trying to filter on that as well. I am reluctant to post my feeble query attempts, but see no other solution than to beg much smarter people:

(self is @current_website)

self.categories.includes(:children, :pages).where('pages.is_published = 1')

This returns mostly what I need, but not Parent Categories without published pages. For instance, it works great if I have:

Parent Category
- Published Page
- Child Category
-- Published Page

Where it fails is when I have no published pages in the parent, like this:

Parent Category
- Child Category
-- Published Page
- Child Category
-- Published Page

Thanks in advance for any help on this. I'm trying to learn as much as I can about queries, but I'm against the wall on this.

UPDATE: Implementing KandadaBoggu's suggestion has yielded much better results, this was added to Category.rb

  has_many :published_pages, :class_name => "Page",
                             :conditions => {:is_published => true}

However, when using the following:

self.categories.where(:parent_id => nil).includes({:children => :published_pages},
                                                   :published_pages)

I get the results I need, but I also get empty Parent Categories (no published_pages, no child categories with published pages. An example:

- Parent Category
-- Published Page
- Parent Category
-- NOTHING

My temporary fix was to appended the query with:

reject{|category| category.pages.empty? && category.children.empty?}

Thanks again for your help.

Answer 1

Add a new association called published_pages (apart from your current associations)

class Category

  has_many   :children,        :class_name => "Category", 
               :foreign_key => "parent_id"
  has_many   :published_pages, :class_name => "Page", 
               :conditions  => { :is_published => true }

end

Now you can get all the categories as follows:

self.categories.includes(:children, :published_pages)

If you are interested in learning why your approach didnt work, read the Rails documentation (scroll 10-15 lines after the Eager loading of associations section). I have included the relevant snippet below:

For example

Post.includes([:author, :comments]).where(['comments.approved = ?', true]).all

This will result in a single SQL query with joins along the lines of:

LEFT OUTER JOIN comments ON comments.post_id = posts.id and 
LEFT OUTER JOIN authors  ON authors.id = posts.author_id. 

Note that using conditions like this can have unintended consequences. In the above example posts with notion approved comments are not returned at all, because the conditions apply to the SQL statement as a whole and not just to the association. You must disambiguate column references for this fallback to happen, for example :order => "author.name DESC" will work but :order => "name DESC" will not.

To eager load filtered rows of an association, use an association with conditions:

class Post < ActiveRecord::Base
  has_many :approved_comments, :class_name => 'Comment', 
             :conditions => ['approved = ?', true]
end

Post.find(:all, :include => :approved_comments)