如何在自引用的has_and_belongs_to_many中查找没有父项的条目

Question

两个模型，第一个是自引用的:

def Page < ActiveRecord::Base
  has_many :source_page_relations,
           :class_name => 'PageRelation',
           :foreign_key => :child_id,
           :dependent => :destroy
  has_many :child_page_relations,
           :class_name => 'PageRelation',
           :foreign_key => :parent_id,
           :dependent => :destroy
  has_many :children, :through => :child_page_relations
  has_many :parents, :through => :source_page_relations
end
def PageRelation < ActiveRecord::Base
  belongs_to :parent, :class_name => 'Page', :foreign_key => :parent_id
  belongs_to :child,  :class_name => 'Page', :foreign_key => :child_id
end

这意味着我可以很容易地通过@page找到父母和孩子。父母和@page.children。现在，问题来了:我如何在全球范围内找到"孤儿"(或者树干，如果你想要像树一样，也就是没有父母)和死角(或者叶子，也就是没有孩子)?我对SQL不太熟悉，所以也许有人有一个快速的想法，如何实现这一目标，而不是在所有页面上迭代的暴力方法?

Answer 1

edit

稍等;实际上，有一个解决方案:使用左外连接。

Page
  .joins("LEFT OUTER JOIN page_relations ON pages.id = page_relations.child_id")
  .where("page_relations.child_id IS NULL")

例如，

this将找到所有trunk页(只是将所有页与其"父"关联连接起来，并选择没有关联的页)。

我不知道这会对性能有什么影响;我认为应该合理的快不要太慢，但不要作为普通任务使用。也许使用Arel Table会更简单(但遗憾的是，我对这些不够熟悉)。

---

我不知道这里是否有除了暴力迭代之外的其他方法(如果你找到一个，我很想知道)。

我的建议是将这些属性捕获为页面的布尔属性，并使用回调来保持它们处于一致的状态。

思路是将before_save, after_save, before_destroy…Page模型上的回调，以便每次我们操作页面时，我们检查它是"主干"还是"叶子"(通过parents.exist?和children.exists?检查它是否有父节点或子节点);然后我们修改这个页面上的布尔trunk和leaf属性(或者在销毁的情况下在关联的页面上)。

这将以某种方式降低插入/更新/删除的性能，但允许通过简单的where( trunk: true )语句快速获取树干和叶子。你可能不得不在Page模型上使用default_scope includes( :parents, :children )(或者至少大量使用includes)来防止DB命中数量爆炸。

也许可以使用相同的策略，但将回调放在PageRelation模型上;甚至可以使用Observer。这完全取决于您的具体需求和编码风格，并且在这里开发要花费很长时间。

Answer 2

查找树叶:

select * from pages where id not in (select parent_id from page_relations)

Answer 3

NOT EXISTS完成了您想要的(并且不像右连接那么难看，也不会遇到像IN那样的NULL问题):

SELECT *
  FROM pages pp
  WHERE NOT EXISTS ( SELECT *
      FROM page_relations pr
      WHERE pr.child_id = pp.id -- Changed
      )
  ;

更新:更改了@Rhywden评论后的WHERE条件