SQL 语句：查询某个主题相关的卡片笔记

ℹ️ 这个查询方案需要 搭配 Query 挂件 和 下面的笔记结构 使用

主题笔记查询结果展示

笔记结构展示

- ### 相关主题1    相关主题2    相关主题3   ……
    - `标签`:#标签1#，#标签2#，……
    内容....
    内容....

完整 SQL 查询语句

WITH
-- >>>>>>>>>>>>  指定关键词.不区分大小写 <<<<<<<<<<<<

-- 1. 包含词（需要包含的词），也可以用查询语句获取
include_terms AS (
    SELECT 'sql' AS term
--  UNION ALL
--     SELECT ...
),

-- 2. 排除词（需要排除的词），也可以用查询语句获取，不需要排除时设置为空字符串
exclude_terms AS (
    SELECT '00-功能测试' AS term
--  UNION ALL
-- SELECT '思源笔记' AS term
),

-- 0. 控制是否启用标签条件筛选，和下面笔记标签 tag_terms 搭配使用，默认不启用标签条件筛选
control_flags AS (
    SELECT 0 AS enable_tag_filter  -- 1表示启用标签筛选，0表示不启用
),

tag_terms AS (
	SELECT '教程' AS user_tag
	--  UNION ALL
	-- SELECT '原理' AS user_tag
),




-- >>>>>> 对关键词预处理，以便给fts表进行match <<<<<<

-- 3. 处理包含词：加双引号转义，用OR连接
include_conditions AS (
    SELECT-- 用括号提升包含词的OR条件的优先级，避免和后面的 NOT 条件产生逻辑顺序混淆
	'(' ||
		GROUP_CONCAT(
			-- 若主题词本身包含双引号，需用两个双引号转义（如 "a""b" 表示 "a"b"）
			'"' || REPLACE(term, '"', '""') || '"', 
			' OR ' 
			)
	|| ')' AS include_query
    FROM include_terms
),

-- 4. 处理排除词：加双引号转义，每个词前加NOT，用空格连接
exclude_conditions AS (
    SELECT 
        -- 若有排除词，拼接为 'NOT "词1" NOT "词2"'；若无则返回空
        CASE WHEN COUNT(term) > 0 
            THEN 'NOT ' || GROUP_CONCAT('"' || REPLACE(term, '"', '""') || '"', ' NOT ') 
            ELSE '' 
        END AS exclude_query
    FROM exclude_terms
),

-- 5. 合并包含和排除条件，生成最终FTS查询
search_conditions AS (
    SELECT 
        -- 拼接包含条件和排除条件（若排除条件非空，加空格分隔）
        include_query || CASE WHEN exclude_query != '' THEN ' ' || exclude_query ELSE '' END 
        AS fts_query
    FROM include_conditions, exclude_conditions
),

-- >>>>>>>>>>>>  查询笔记卡片和内容标题 <<<<<<<<<<<<

-- 1. 查询卡片
note_card AS (
	SELECT DISTINCT
	b.id, b.parent_id,
	b.content, b.hpath
	FROM blocks b
	JOIN blocks_fts_case_insensitive fts ON b.rowid = fts.rowid
	WHERE 
		fts.content MATCH (SELECT fts_query FROM search_conditions)
		AND b.fcontent = ''
		AND b.markdown LIKE '### ((%'
),

-- 2. 查询卡片的标签

common_blocks AS (
    SELECT 
        b1.id, 
        b1.tag
    FROM 
        blocks_fts b1
    JOIN 
        note_card nc ON b1.id = nc.parent_id
),
-- 预先计算出 FTS 匹配的 ID 列表
matched_ids AS (
    SELECT id
    FROM blocks_fts
    WHERE tag MATCH (SELECT user_tag FROM tag_terms)
),

note_tag AS (
	SELECT
	b1.id, b1.tag
	FROM common_blocks b1
	CROSS JOIN control_flags cf
	WHERE 
        (cf.enable_tag_filter = 0)
        OR 
		( cf.enable_tag_filter = 1 
			AND b1.id IN (SELECT id FROM matched_ids)
		)
),


-- 3. 查询卡片的内容标题
note_title AS (
	SELECT
	 b1.parent_id,b1.id, 
	 b1.type,
	 b1.content, b1.hpath
	FROM blocks b1
	JOIN note_card nc
	WHERE
		b1.subtype in ('h3','h4','h5','h6')
		AND b1.parent_id in (
			SELECT nc.id FROM note_card nc
		)
		AND  b1.parent_id = nc.id
)


-- >>>>>>>>>>>>  主查询：应用最终的FTS条件 <<<<<<<<<<<<

SELECT

    nc.content AS "__1____pre__主题",
--     group_concat(nt.content,'<br>') AS "__2____pre__主要内容",
	 '**'|| (CASE 
		WHEN instr(nt.content,'http')>0
			THEN substring( nt.content, 1, instr(nt.content,'http')-2 )
		ELSE nt.content
	END
	) || '**'  AS "__2____pre__内容标题",
	ntag.tag  AS "__3__标签",
    "[" || nc.hpath || "](siyuan://blocks/" || nc.id || ")" AS "__4____pre__目录"

FROM note_card nc
LEFT JOIN note_title nt on nt.parent_id = nc.id
JOIN note_tag ntag on ntag.id = nc.parent_id
ORDER BY nc.hpath DESC