递归查询以检查是否启用了所有父项 [英] Recursive query to check that all parents are enabled

问题描述

我有一个 CMS 系统，它有一个带有父子关系的站点地图表和一个内容表.有时我不想在查询中包含相应的站点地图条目或其任何父项被禁用的内容.

I have a CMS system which has a sitemap table with a parent-child relationship and a content table. Sometimes I don't want to include content in queries if it's corresponding sitemap entry or any of its parents is disabled.

基本的表结构是:

tb_Sitemap:id、parent_id、启用

tb_Sitemap: id, parent_id, enabled

tb_Content:id、sitemap_id

tb_Content: id, sitemap_id

所以我希望能够像这样在我的查询中添加一些内容:

So I want to be able to add something to my queries like this:

SELECT * FROM tb_Content WHERE {tb_Sitemap.enabled and any or all parents are also enabled}

我知道我需要使用 CTE，但我不确定如何将这些添加到 WHERE 子句或如何去做.

I know I need to use a CTE but I am unsure about how to add these to a WHERE clause or how to go about it.

我猜我需要做类似的事情，但不确定如何添加到 WHERE 子句中:

I am guessing I need do something like, but not sure how to add to a WHERE clause:

;WITH cte (enabled)
AS
(
SELECT enabled FROM tb_Content WHERE id = tb_Content.sitemap_id
UNION ALL
SELECT CASE WHEN b.enabled != 1 THEN 0 ELSE a.enabled FROM tb_Sitemap a
INNER JOIN cte b ON a.parent_id = b.id
)
SELECT enabled FROM cte

示例数据:

tb_站点地图

• id:1，parent_id:null，启用:1
• id:2，parent_id:1，启用:1
• id:3，parent_id:2，启用:1
• id:4，parent_id:1，启用:0
• id:5，parent_id:4，启用:1
• id:6，parent_id:5，启用:1

tbl_Content

tbl_Content

• sitemap_id: 3(这会出现是因为 sitemap_id:3 已启用，因为它的所有父项都已启用)
• sitemap_id: 6(这不会出现，因为虽然启用了 sitemap_id:6，但它的父级之一未启用)

推荐答案

-- A little test data.
declare @tb_Sitemap as table ( id int, parent_id int null, enabled bit )
insert into @tb_Sitemap ( id, parent_id, enabled ) values
  ( 1, NULL, 1 ), ( 2, 1, 1 ), ( 3, 2, 1 ),
  ( 4, 1, 0 ), ( 5, 4, 1 ), ( 6, 5, 1 )
declare @tb_Content as table ( sitemap_id int )
insert into @tb_Content ( sitemap_id ) values ( 3 ), ( 6 )

-- Query the little beggars.
; with CTE as (
  -- Start at the root(s).
  select id, parent_id, enabled, enabled as summary_enabled
    from @tb_Sitemap
    where parent_id is NULL
  union all
  -- Add one generation at a time.
  select S.id, s.parent_id, s.enabled, cast( case when s.enabled = 1 and CTE.summary_enabled = 1 then 1 else 0 end as bit )
    from CTE inner join
      @tb_Sitemap as S on S.parent_id = CTE.id
  )
select *, case when summary_enabled = 1 and sitemap_id is not NULL then '< winner!' else '' end as include
  from CTE left outer join
    @tb_Content as C on C.sitemap_id = CTE.id

这篇关于递归查询以检查是否启用了所有父项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！