postgreHow使用递归计算sql中父类别的所有后代

Question

我使用prism为我的数据库模式。Schema on Prisma:

model Category {
id                Int              @id @default(autoincrement())
name              String
parent_crawler_id Int?
url_slug          String?
createdAt         DateTime         @default(now())
updatedAt         DateTime         @updatedAt
item_categories   ItemCategories[]
crawler_id        Int              @unique @default(1)
orderIndex        Int?
path              String?
Item              Item[]
}
model ItemCategories {
id          Int      @id @default(autoincrement())
item_id     Int
item        Item     @relation(fields: [item_id], references: [id], onDelete: Cascade)
category_id Int
category    Category @relation(fields: [category_id], references: [id])
createdAt   DateTime @default(now())
updatedAt   DateTime @updatedAt
}

类别有蔬菜和水果两类。蔬菜类的crawler_id为1，水果类的crawler_id为2。水果有一个孩子，即有机，id为10。Organic的parent_crawler_id为2。有机有一个孩子，即苹果。苹果的parent_crawler_id为10,id为11。在ItemCategories表中，附加的category_id为10和11。

我们如何在sql中使用递归方法计算附加到ItemCategories表中的项目的水果和蔬菜类别的总后代?

如ItemCategories表

id      category_id     item_id
1       10              1
2       11              2

Answer 1

我想这应该可以回答你更新的问题:

WITH RECURSIVE category_tree AS (
SELECT 
c1.id,
c1.name,
c1.parent_crawler_id,
c1.crawler_id
FROM Category c1
WHERE c1.crawler_id IN (1, 2) -- Veg (crawler_id: 1) and Fruit (crawler_id: 2)
UNION ALL
SELECT
c2.id,
c2.name,
c2.parent_crawler_id,
c2.crawler_id
FROM Category c2
JOIN category_tree ct ON c2.parent_crawler_id = ct.id
)
SELECT
ct.crawler_id,
COUNT(DISTINCT ic.item_id) AS total_descendants
FROM category_tree ct
JOIN ItemCategories ic ON ic.category_id = ct.id
GROUP BY ct.crawler_id;