如题，我现在想实现数据库模板列显示主键所在的二级标题，我用的是这个模板 基于思源笔记的数据库使用分享 (附模板)
其中的项目列会自动获取任务所在的文档名称，我希望能继续获取任务对应的二级标题名称显示在后面
比如下图，文档名称是工作备忘，想多显示工时登记这个任务对应的二级标题工时/周报，最后模板列输出工作备忘-工时/周报，请大佬帮忙实现，感谢。
相关帖子
被采纳的回答
-
wilsons •付费者 捐赠者
刚才看你代码，感觉你这里的主键 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 "-") }
请输入回帖内容
...
-
简单方式，分开查询，然后用模板拼接起来应该可以吧
select content as title from blocks where id = '20250126175945-876nap9'; select content as h2 from blocks where root_id='20250126175945-876nap9' and type = 'h' and subtype='h2';或者合并到一条 SQL
SELECT (SELECT content FROM blocks WHERE id = '20250126175945-876nap9') AS title, (SELECT content FROM blocks WHERE root_id = '20250126175945-876nap9' AND type = 'h' AND subtype = 'h2') AS h2;或者用 join
SELECT b1.content AS title, b2.content AS h2 FROM blocks b1 LEFT JOIN blocks b2 ON b1.id = '20250126175945-876nap9' AND b2.root_id = '20250126175945-876nap9' AND b2.type = 'h' AND b2.subtype = 'h2' WHERE b1.id = '20250126175945-876nap9' LIMIT 1;1 回复 -
刚才看你代码，感觉你这里的主键 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 "-") }