如果没有N+1，我如何在自引用表中找到记录的每个上级

Question

我正在使用Ruby on Rails应用程序中的自引用模型(Category)，它包含以下各列：

id、name、level、parent_id

belongs_to :parent, class_name: 'Category', optional: true

其概念是level 1类别可以具有level 2子类别，它可以具有level 3子类别，等等。

例如：

Category id: 1, name: 'Dessert', level: 1, parent_id: nil

Category id: 2, name: 'Cold', level: 2, parent_id: 1

Category id: 3, name: 'Cake', level: 3, parent_id: 2

Category id: 4, name: 'Ice Cream', level: 3, parent_id: 2

Category id: 5, name: 'Sponge', level: 4, parent_id: 3

我希望找到记录的每一个上升部分，不管它有多少levels深度。然后，我想以升序将所有名称连接到一个字符串中。

也就是说，如果我从Sponge开始，我想要一个返回"Dessert - Cold - Cake - Sponge"的方法

到目前为止，我用的是n+1，但感觉不是很好：

def self.concatenate_categories(order)
      category = order.category
      categories_array = []

      order.category.level.times do
        categories_array.push category.name
        category = Category.find(category.parent_id) if category.parent_id.present?
      end

      categories_array.reverse.join(' - ')
    end

如果此订单是针对Sponge的，则我将获得"Dessert - Cold - Cake - Sponge"。如果订单是Cake的，我会得到"Dessert - Cold - Cake"。

Answer 1

您可以尝试递归CTE，以根据其parent_id获取每个类别的父类别：

WITH bar AS (
  WITH RECURSIVE foo AS (
    SELECT
      categories.id,
      categories.name,
      categories.parent_id
    FROM categories
    WHERE categories.id = 5
    UNION
    SELECT
      p.id,
      p.name,
      p.parent_id
    FROM categories p
    INNER JOIN foo f
    ON f.parent_id = p.id
  ) SELECT name FROM foo ORDER BY id
) SELECT STRING_AGG(name, ' - ') FROM bar 

Answer 2

这个怎么样？我还没有测试过这段代码，但是你已经明白了，连接的次数和级别的次数一样多，查询一次。取决于有多少个级别，您的解决方案可以比太多的连接更快。

def self.concatenate_categories(order)
  scope = order.category

  categories_array = if category.level > 1
    scope = scope.select('categories.name')
    order.category.level.downto(1) do |l|
      scope = scope.joins("JOIN categories as c#{l} ON categories.id = c#{l}.parent_id")
                   .select("c#{l}.name")
    end
    scope.to_a
  else
    Array.wrap(scope.name)
  end
  categories_array.reverse.join(' - ')
end