【分享】利用 SQL 以两种方法实现任务管理

本贴最后更新于 471 天前,其中的信息可能已经沧海桑田

前提

本文分享的 SQL 代码推荐配合以下规定格式(无序列表 + 任务)使用:

  • 任务分类(自定义,最好使用块引用,方便使用反链汇总)

    • 具体任务 1
    • 具体任务 2
    • ...

方法 1——利用嵌入块

实现代码

1.已完成任务

SELECT * FROM blocks WHERE (id in (SELECT parent_id FROM blocks WHERE type='l' AND markdown LIKE '%[x]%'  AND subtype='t' )) ORDER BY updated DESC

2.待办任务

SELECT * FROM blocks WHERE (id in (SELECT parent_id FROM blocks WHERE type='l' AND markdown LIKE '%[ ]%'  AND subtype='t' )) ORDER BY updated DESC

实现效果

image.png

特点

  • 优点:
    1. 可以同时显示任务项和所属分类。
    2. 即使未按照规定格式记录任务,也可通过任务记录所在的文档名称显示任务项的从属关系。
    3. 所有任务都能够直观显示。
  • 缺点:
    1. 无法直观显示任务的创建时间和更新(完成)时间。
    2. 并列的任务(同一个分类下多个子任务)如果只有部分完成则无法完全区分开,“已完成任务”和“待完成任务”中都将显示。
    3. 所有任务都显示出来占据篇幅大,可以通过 LIMIT 限制。

方法 2——利用 Query 挂件

实现代码

1.已完成任务

SELECT
    CASE
        WHEN b.fcontent == '' 
        THEN '==**未分类**=='
        ELSE b.fcontent 
    END AS __1____pre__任务分类,
    '['||
        CASE
            WHEN length(replace(b.content,b.fcontent,''))>16
            THEN substr(replace(b.content,b.fcontent,''),1,16)||'...'
            ELSE replace(b.content,b.fcontent,'')
        END||'](siyuan://blocks/'||b.id||')' AS __2____pre__已完成任务列表,
    b.created AS __3____datetime__创建时间,b.updated AS __4____datetime__更新时间,
    '['||b.hpath||'](siyuan://blocks/'||b.id||')' AS __5____pre__任务路径
FROM
    blocks AS b
WHERE
    ( id in
        (SELECT parent_id FROM blocks
        WHERE
        (type = 'l'
        AND
            markdown LIKE '%[x]%'
        AND
            subtype = 't'
        AND
            hpath NOT LIKE '%从这里开始%'
        )
        )
    )
ORDER BY
    b.updated DESC
LIMIT 20

2.待办任务

SELECT
    CASE
        WHEN b.fcontent == '' 
        THEN '==**未分类**=='
        ELSE b.fcontent 
    END AS __1____pre__任务分类,
    '['||
        (CASE
            WHEN length(replace(b.content,b.fcontent,''))>16
            THEN substr(replace(b.content,b.fcontent,''),1,16)||'...'
            ELSE replace(b.content,b.fcontent,'')
        END)||'](siyuan://blocks/'||b.id||')' AS __2____pre__待完成任务列表,
    b.created AS __3____datetime__创建时间,b.updated AS __4____datetime__更新时间,
    '['||b.hpath||'](siyuan://blocks/'||b.id||')' AS __5____pre__任务路径
FROM
    blocks AS b
WHERE
    ( id in
        (SELECT parent_id FROM blocks
        WHERE
        (type = 'l'
        AND
            markdown LIKE '%[ ]%'
        AND
            subtype = 't'
        AND
            hpath NOT LIKE '%从这里开始%'
        )
        )
    )
ORDER BY
    b.updated DESC
LIMIT 20

实现效果

image.png

image.png

特点

  • 优点:
    1. 显示效果更直观,包括任务分类、任务项、创建时间、更新(完成)时间、任务记录的位置。
    2. 对于任务项字数比较多的问题,为避免列宽过大,仅显示前 16 个字符。
    3. 占据篇幅更小。
  • 缺点:
    1. 需要严格按照规定格式记录任务,否则显示效果不佳(如上图标注)。
    2. 并列的任务(同一个分类下多个子任务)在列表中只会显示第一个任务的文字,要查看未完成/已完成的任务需要通过悬浮窗查看。

总结

  • 以上方法参考了其他用户的帖子,感谢。
  • 方法 1 和方法 2 各有优缺点,目前还不完美,大家根据需要选用。
  • 本文所使用的任务记录方法不限制记录位置,可以随时随地记录,推荐在 daily note 中记录。任务分类(块引用)放在无序列表中,方便后期利用反链整理汇总。
  • 思源笔记

    思源笔记是一款隐私优先的个人知识管理系统,支持完全离线使用,同时也支持端到端加密同步。

    融合块、大纲和双向链接,重构你的思维。

    17926 引用 • 65754 回帖 • 2 关注

相关帖子

欢迎来到这里!

我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。

注册 关于
请输入回帖内容 ...
  • forwardlee

    很强,效果也不错,但是还是没怎么看懂,可能我太弱了

    1 回复
  • 嵌入块的用法可以查看自带用户指南,Query 挂件需要到集市中下载。

  • foolishman83 1 赞同 1 评论

    说实话,思源笔记对于任务列表的特性开发的不够,和 logseq 相比有一定差距,在待办事务管理上还有完善空间。比如任务列表的完成时间没有专有字段,只能通过更新时间来勉强匹配,比如设定将来特定时间的待办任务存在困难等等,希望在以后的版本中可以借鉴其他笔记软件的长处,虽然不至于要求 all in one,但基本的待办管理和事务笔记能够较好地结合,思源笔记的应用场景将会更大些。

    2 回复
    logseq 本身出来的时候就是参考了其他软件的任务管理功能,思源大概率是不会往这方面去做的
    mozhu
  • 对的,现在的任务场景没有单独作为一个功能点划分出来,所以没有其特有的功能。

  • 之前我也追求这个。后面妥协了,直接 iframe 滴答清单

  • canxer

    就是因为任务管理功能太鸡肋所以我现在 Siyuan 和 Logseq 双修 😂

  • xjacr 1

    针对 Query 挂件方式的缺点 2,我用了下面的代码改进:

    SELECT *
    FROM blocks
    WHERE parent_id in (
    	SELECT id FROM blocks WHERE parent_id in (
    		SELECT parent_id FROM blocks WHERE id in (
    			SELECT block_id FROM refs WHERE def_block_id  = '被引用块的块 ID'
    		)
    	)
    );
    

    效果如下:

    image.png

    image.png

    当然,该改进适用于父节点存在引用块的情况

    添加以下条件筛选标签是 todo 的代办任务

    
    WHERE 
    parent_id in (
    	SELECT id FROM blocks WHERE parent_id in (
    		SELECT parent_id FROM blocks WHERE id in (
    			SELECT block_id FROM refs WHERE def_block_id  = '20231125150551-7tr1dis'
    		)
    	)
    )
    AND type = 'i'
    AND subtype = 't'
    AND markdown LIKE '%[ ]%'
    AND tag = '#todo#'
    

    筛选条件修改成 AND markdown LIKE '%[x]%' 即为 todo 标签下的已办任务

    添加上 SELECT 条件:

    SELECT
        (SELECT '[' || fcontent || '](siyuan://blocks/' || id || ')'
    		FROM blocks WHERE id = (
    			SELECT parent_id FROM blocks WHERE id = b.parent_id
    	)) AS __1____pre__项目,
    	'['||
            (CASE
                WHEN length(b.content)>16
                THEN substr(b.content,1,16)||'...'
                ELSE b.content
            END)||'](siyuan://blocks/'||b.id||')' AS __2____pre__待完成任务,
    	b.created AS __3____datetime__创建时间,
    	b.updated AS __4____datetime__更新时间,
        '['||b.hpath||'](siyuan://blocks/'||b.id||')' AS __5____pre__任务路径,
    	tag AS __6____pre__标签
    FROM blocks AS b
    WHERE 
    parent_id in (
    	SELECT id FROM blocks WHERE parent_id in (
    		SELECT parent_id FROM blocks WHERE id in (
    			SELECT block_id FROM refs WHERE def_block_id  = '20231125150551-7tr1dis'
    		)
    	)
    )
    AND type = 'i'
    AND subtype = 't'
    AND markdown LIKE '%[ ]%'
    AND tag = '#todo#'
    
    

    最终效果如下:

    image.png

    20231125 总结:

    1、该改进方案适用于大纲列表中,父节点是引用块类型,子节点是任务类型的

    2、该改进方案适合项目任务收集,这也是我改进的目的

    20231126 进一步改进

    新需求:之前的方案只能是上下层级,实际上代办事项可能在引用块的任意层的子节点上,比如:

    image.png

    需要筛选出 xxx 项目下第三级的待办列表

    由于思源笔记底层是 SQLite 数据库,可以尝试试用递归的方式查询出父节点下的所有子节点

    递归代码如下:

    with recursive digui as
    (
    SELECT * from blocks where id = '20231126000050-rbrlqlf'
    UNION ALL
    SELECT blocks.* from digui JOIN blocks ON blocks.parent_id = digui.id
    )
    select
    	content,
    	fcontent,
    	hpath,
    	markdown,
    	type,
    	subtype
    from digui
    where
    type = 'i'
    AND subtype = 't'
    
    

    看下效果:

    image.png

    根据父节点的 ID 可以筛选出所有的待办

    结合递归代码再次改进,代码如下:

    WITH RECURSIVE digui AS
    -- 初始查询
    (
    SELECT
    	*,
    	'' AS level,
    	'' AS grandparent_id,
    	'20231125150551-7tr1dis' AS def_block_id,	-- 被引用块的块 ID
    	(SELECT content FROM blocks_fts WHERE id = '20231125150551-7tr1dis') AS def_block_content	-- 被引用块的 content
    FROM blocks_fts
    WHERE parent_id IN (
    	SELECT id FROM blocks_fts WHERE parent_id IN (
    		SELECT parent_id FROM blocks_fts WHERE id IN (
    			SELECT block_id FROM refs WHERE def_block_id  = '20231125150551-7tr1dis'	-- 被引用块的块 ID
    		)
    	)
    )
    UNION ALL
    -- 递归查询
    SELECT
    	blocks_fts.*,
    	digui.level + 1,
    	digui.parent_id,
    	digui.def_block_id,
    	digui.def_block_content
    FROM digui JOIN blocks_fts ON blocks_fts.parent_id = digui.id
    -- 判断递归终止条件,根据具体情况适配
    WHERE digui.subtype != ''
    )
    
    SELECT
    	'((' || b.def_block_id || ' "' || b.def_block_content || '"))' AS __0____pre__根节点,
        (SELECT '[' || fcontent || '](siyuan://blocks/' || id || ')' FROM blocks_fts WHERE id = b.grandparent_id) AS __1____pre__上级节点,
    	'['||
            (CASE
                WHEN length(b.content)>16
                THEN substr(b.content,1,16)||'...'
                ELSE b.content
            END)||'](siyuan://blocks/'||b.id||')' AS __2____pre__待完成任务,
    	b.created AS __3____datetime__创建时间,
    	b.updated AS __4____datetime__更新时间,
        '['||b.hpath||'](siyuan://blocks/'||b.id||')' AS __5____pre__任务路径,
    	b.tag AS __6____pre__标签
    FROM digui AS b
    WHERE
    subtype != ''	-- 排除最后一级节点
    AND level != ''	-- 排除根节点
    AND type = 'i'	-- i: 列表项块
    AND markdown LIKE '%[ ]%'	-- %[ ]%: 代办,%[x]%: 已办
    AND tag LIKE '%#todo#%'	-- 包含 todo 标签
    
    

    效果如下:

    image.png

    20231126 总结

    1、基于上一版本改进,支持引用块下任意节点代办任务筛选

请输入回帖内容 ...