see https://ld246.com/article/1743473205973

需求：

比如如下数据库中，主键id是事件列，比如图中的任务项“工时登记”；

然后项目列是模板列，在这个模板中获取“工作备忘”这个文档的文档标题和h2标题（通过文档中的任务项“工时登记”的块id获取，这个id也同时是数据库中的主键id）；

即通过任务块的id获取该文档的标题和任务块的上级h2标题，然后实现方式是用数据库的模板列获取；

解决办法如下：

see https://ld246.com/article/1743473205973/comment/1743482627371?r=wilsons#comments

刚才看你代码，感觉你这里的主键 id 应该是块 id，不是文档 id，上面的 SQL 仅对主键 id 是文档 id 才有效

如果主键是块 id，加上模板仅支持 select * 查询

需要用下面的 SQL，分别查询标题和 h2，然后用模板函数 cat 拼接到一起

查标题还用你之前的 SQL 就行

select * from blocks where id in (select root_id from blocks where id='20250401135156-8k4wks2');

查 h2 用下面这个，通过 parent_id 递归查询

WITH RECURSIVE ancestors AS (
    -- 初始查询：从已知 id 开始
    SELECT 
        *
    FROM 
        blocks
    WHERE 
        id = '20250401135156-8k4wks2'

    UNION ALL

    -- 递归查询：通过 parent_id 向上遍历
    SELECT 
        b.*
    FROM 
        blocks b
    INNER JOIN 
        ancestors a
    ON 
        b.id = a.parent_id
)
-- 最终查询：筛选满足条件的记录
SELECT 
    content as h2
FROM 
    ancestors
WHERE 
    type = 'h' AND subtype = 'h2';

别忘了在模板中具体 id 值改成?号

转换为模板代码示例如下

.action{ $h2Blocks := queryBlocks `WITH RECURSIVE ancestors AS (SELECT * FROM blocks WHERE id = '?' UNION ALL SELECT b.* FROM blocks b INNER JOIN ancestors a ON b.id = a.parent_id) SELECT * FROM ancestors WHERE type = 'h' AND subtype = 'h2'` .id }
.action{ $blocks := queryBlocks "select * from blocks where id in (select root_id from blocks where id='?')" .id }
.action{ if and (not (empty $blocks)) (not (empty $h2Blocks)) }
    .action{ $blockContent := (cat (first $blocks).Content "-" (first $h2Blocks).Content ) }
    .action{$blockContent}
.action{ end }

模板中用 cat 函数会在 - 前后产生一个空格，如果不想要这个空格，如下解决

把

.action{ $blockContent := (cat (first $blocks).Content "-" (first $h2Blocks).Content ) }

改为

.action{ $blockContent := (list (first $blocks).Content (first $h2Blocks).Content | join "-") }

完整代码如下：

.action{ $h2Blocks := queryBlocks `WITH RECURSIVE ancestors AS (SELECT * FROM blocks WHERE id = '?' UNION ALL SELECT b.* FROM blocks b INNER JOIN ancestors a ON b.id = a.parent_id) SELECT * FROM ancestors WHERE type = 'h' AND subtype = 'h2'` .id }
.action{ $blocks := queryBlocks "select * from blocks where id in (select root_id from blocks where id='?')" .id }
.action{ if and (not (empty $blocks)) (not (empty $h2Blocks)) }
    .action{ $blockContent := (cat (first $blocks).Content "-" (first $h2Blocks).Content ) }
    .action{ $themeTODO := .事件 }
    .action{ if ne $blockContent $themeTODO }
        <!-- 计算内容长度 -->
        .action{ $contentLength := len $blockContent }

        <!-- 提取前 3 个字符的 ASCII 值，增加扰动 -->
        .action{ $firstChar := substr 0 1 $blockContent }
        .action{ $secondChar := substr 1 2 $blockContent }
        .action{ $thirdChar := substr 2 3 $blockContent }
        .action{ $asciiValue1 := atoi $firstChar }
        .action{ $asciiValue2 := atoi $secondChar }
        .action{ $asciiValue3 := atoi $thirdChar }
        .action{ $asciiSum := add (add $asciiValue1 $asciiValue2) $asciiValue3 }

        <!-- 独立扰动种子 -->
        .action{ $hashR := mod (add (mul $asciiValue1 97) (mul $asciiSum $contentLength)) 16777215 }
        .action{ $hashG := mod (add (mul $asciiValue2 41) (mul $asciiSum (add $contentLength 13))) 16777215 }
        .action{ $hashB := mod (add (mul $asciiValue3 59) (mul $asciiSum (sub $contentLength 7))) 16777215 }

        <!-- 计算 RGB 值 -->
        .action{ $r := mod (add (mul $hashR 23) (mul $asciiValue1 11) (mul $contentLength 17)) 256 }
        .action{ $g := mod (add (mul $hashG 17) (mul $asciiValue2 19) (mul $contentLength 13)) 256 }
        .action{ $b := mod (add (mul $hashB 29) (mul $asciiValue3 7) (mul $contentLength 29)) 256 }

        <!-- 提亮并调整范围 -->
        .action{ $r := max 60 (min $r 255) }
        .action{ $g := max 60 (min $g 255) }
        .action{ $b := max 60 (min $b 255) }

        <!-- 引入波动性和扰动 -->
        .action{ $rand := mod (add $asciiSum (mul $contentLength 3)) 100 }
        .action{ $r := mod (add $r $rand) 256 }
        .action{ $g := mod (add $g $rand) 256 }
        .action{ $b := mod (add $b $rand) 256 }

        <!-- 生成颜色代码 -->
        .action{ $color := printf "rgb(%d, %d, %d)" $r $g $b }

        <!-- 渲染带颜色的内容 -->
        <span style="color: .action{$color};">.action{$blockContent}</span>
    .action{ else }
        无
    .action{ end }
.action{ else }
    无
.action{ end }