mysql - Rails 3 - 有条件的预加载

Question

好吧，我完全被这个难住了。我正在尝试构建一个按类别组织的已发布网页的菜单。

类别.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

页面.rb

belongs_to :category

Page 模型也有 :is_published，所以我也在尝试对其进行过滤。我不愿意发布我微弱的查询尝试，但除了乞求更聪明的人之外别无其他解决方案:

(自己是@current_website)

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

这主要返回我需要的内容，但不返回没有已发布页面的父类别。例如，如果我有:

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

失败的地方是我在父级中没有已发布的页面，如下所示:

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

提前感谢您对此提供的任何帮助。我正在尝试尽可能多地了解查询，但我对此持反对态度。

更新:实现 KandadaBoggu 的建议产生了更好的结果，这已添加到 Category.rb

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

但是，当使用以下内容时:

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

我得到了我需要的结果，但我也得到了空的父类别(没有 published_pa​​ges，没有包含已发布页面的子类别。示例:

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

我的临时修复是在查询中附加:

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

再次感谢您的帮助。

Answer 1

添加一个名为 published_pa​​ges 的新关联(除了您当前的关联)

class Category

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

end

现在你可以得到所有的分类如下:

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

如果您有兴趣了解为什么您的方法不起作用，请阅读 Rails documentation (在 Eager loading of associations 部分之后滚动 10-15 行)。我在下面包含了相关的片段:

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.

要预先加载关联的过滤行，请使用带条件的关联:

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

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