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 Categor
Edit
Report