[js] 告别 select * from blocks!嵌入块多字段查询来了

前言

众所周知,思源嵌入块查询(输入{{}}弹出查询窗的那个)不支持指定字段查询,只能使用类似这样的 SQL 查询。

select * from blocks limit 3

那么,你想像下面这样查询吗?

select content, created from blocks where type='d' and trim(content) != '' limit 2

你想轻松实现这样的效果吗?

image.png

只需要下面两个 SQL 就可以立即实现上面的效果:

-- style created {font-weight:bold;color:red;}
select content,created from blocks where type='d' and trim(content) != '' limit 2
select content,created from blocks where type='p' and trim(markdown) != '' limit 2

你 💓 心动了吗?

心动不如行动,你只需要安装下面的代码,即可立即实现上面的查询效果。

👉 代码

功能介绍

  1. 支持多字段查询,比如 select id, markdown, created from...
  2. 可指定字段样式及指定字段显示顺序
  3. 可以格式化常用字段数据
  4. 可以通过 js 代码自定义格式化字段数据
  5. SQL 中支持 {{CurrDocId}}{{CurrBlockId}} 标记,分别代表当前文档 id 和当前嵌入块 id(有时需要排除当前嵌入块时有用)
  6. SQL 查询出现错误时,可把错误显示到页面上和控制台,思源默认不显示错误信息

使用手册

常用指令

样式指令 :-- style 字段名 {样式内容},例如:-- style created {color:red;}
格式化指令 :-- format 字段名 函数名,例如:-- format created datetime,默认 created,updated 字段是 datetime
js 格式化指令 :-- jsformat 字段名 {js 代码},例如:-- jsformat updated {return 'hello '+fieldValue},字段的值会被 return 的结果覆盖,默认可使用的变量,embedBlockID,currDocId,currBlockId,fieldName,fieldValue,originFieldValue
渲染指令 :-- render 字段名 true/false,例如:-- render markdown false,只有 markdown 字段有效,默认 true
隐藏字段指令 :-- hide 字段名, 字段名, ...,例如:-- hide id, created, ...,隐藏字段也可以在 sql 的字段上写标记,比如,select id__hide, content from...,有别名的需要加到别名上,比如,-- hide path2 或 select path as path2__hide, ...,但 id 不能用别名且只能小写,否则无法被识别为 id
字段排序指令 :-- sort 字段名, 字段名, ...,例如:-- sort id, content, created,字段的将按照这个指定的顺序显示
强制使用自定义 SQL -- custom true,默认情况下只有一个 select * from 的 SQL 被认为是思源默认 SQL(思源默认 SQL 只能返回块 Markdown 一个字段),但当使用该指令时,则强制认为是自定义 SQL。
强制不处理隐藏字段 -- not-deal-hide true,默认情况,使用 select id__hide, content from...,会把__hide 认为隐藏字段,但当使用该指令时,会忽略__hide 标记

注意:要想点击可以弹出预览窗,必须加 id 字段,如果不想 id 字段被显示出来,可以用 id__hide 隐藏字段(之前尝试过自动加 id 方案,但在复杂查询时会有问题)。

当多个相同字段的指令时,后面的会覆盖前面的。

JS 格式化指令常用变量

embedBlockIDcurrBlockId 这两个变量是同一个意思,即当前嵌入块的块 id
currDocId 当前文档的 id
fieldName 当前字段的字段名,比如 id, root_id, content 等
fieldValue 当前字段的值,可能是格式化后的结果
originFieldValue 当前字段的原始值,即数据库中的值

常用格式化函数

datetime 格式为 年-月-日 时:分:秒
date 格式为 年-月-日
time 格式为 时:分:秒
type 把类型转换为文字描述,默认 type 字段已格式化
subtype 把子类型转换为文字描述,默认 subtype 字段已格式化

高级指令

支持多行注释的指令,仅 jsformat 和 style 支持,通常用于编写复杂代码。

格式:

/* jsformat 字段名 {
    // your codes
}*/

/* style 字段名 {
    // your codes
}*/

如下示例

/*
jsformat content {
    // your codes
}
*/
/*
style content {
    // your codes
}
*/
select id,content,created,... from blocks ....

js 指令

只要在查询代码中添加 -- js-- js true 即可。

然后,就可以在代码中直接写 js 了。

比如:

-- js
-- style content {color:red}
return await querySql(`select content, id__hide from blocks where type='d' limit 1`, errors);

js 指令中也支持其他指令,比如这里 style 指令等。

这里可以直接用 await 不需要事先在外层加 async 函数包裹。

可以返回一个包含 key,value 的对象数组,key 是字段名,value 是字段值;也可以返回一个 id 数组。

querySql 函数是 js 指令自带函数,传入 errors 参数,当 SQL 查询报错时可以显示错误。

类似 querySql 的函数或变量还有:

embedBlockID, 本嵌入块 id

currDocId, 当前文档 id

currBlockId, 本嵌入块 id

whenElementExist, 等待元素或变量出现函数

fetchSyncPost, 请求 api 函数

protyle, 编辑器对象

top, 编辑器滚动条距顶部高度,通常不需要

querySql, 通过 SQL 查询数据函数

pick, 从对象数组中仅保留指定的 key 值,通常用于仅保留指定的字段,比如 pick(result, 'content', 'id__hide') 或 pick(result, ['content', 'id__hide'])

unpick, 从对象数组中排除指定的 key 值,通常用于排除指定的字段

errors, 错误对象,在 SQL 查询中传入这个参数,当查询报错时可显示到页面中。

showErrors, 主动显示错误,如果需要时可调用。

blocks, getFieldsHtml, getBlock, meta 这些参数仅在需要高级自定义返回结果时才需要,如果不清楚,忽略即可。

结果返回一个数组对象即可,比如 [{content:'', id:'', ...}, ...],然后用 pick 或 upick 过滤需要的字段即可。也可以返回一个 id 数组(会返回 blocks 表的所有字段)。

字段间数据共享

通过 jsformat 指令可以共享字段间的数据

比如

-- sort id, content
-- jsformat id {setShareData('id', originFieldValue.split('-').pop());}
-- jsformat content {return getShareData('id') +'-'+ fieldValue}
select id, content from blocks where type = 'd' order by icreated desc limit 2;

当获取不到数据时,使用 sort 指令可保证字段的执行顺序。

兼容性说明

  • 仅支持 SQL 方式查询,不支持 //!js 方式查询,//!js 还是思源自带的方式查询。
  • 但,如果想用 js 代码查询,且支持上述其他指令,可以把 //!js 换成 -- js 即可,详情可参考 js 指令

使用示例

示例 1:多字段查询

select content, created from blocks where type='d' and trim(content) != '' limit 2

示例 2:通过指令和字段标记控制字段隐藏

这里通过-- hide 指令和字段加__hide 后缀实现隐藏字段,注意,有别名的需要加在别名上

-- hide path2
select id__hide, path as path2, hpath as hpath2__hide, content, created from blocks where type = 'd' limit 2;

示例 3:通过指令控制样式和格式化

-- style created {font-weight:bold;float:right;color:red;}
-- format created datetime
select content, created from blocks where type='d' and trim(content) != '' limit 2

示例 4:控制 Markdown 渲染

-- render markdown false
select markdown, created from blocks where type='p' and trim(markdown) != '' limit 2

示例 5:字段排序指令

-- sort content, created
select content, created from blocks where type='d' and trim(content) != '' limit 2

示例 6:在 SQL 中使用 {{CurrDocId}}{{CurrBlockId}} 标记

select * from blocks where type = 'p' and root_id='{{CurrDocId}}' and id <> '{{CurrBlockId}}' limit 2;

示例 7:生成统计

统计总数

image.png

select '总文档数:', count(*) as count from blocks where type = 'd';
统计本周更新文档

image.png

-- sort weekday, count
-- style count {margin-left: 50px;}
WITH
    -- 1)定义一周七天的表,day_no 用于排序,name 为要显示的中文星期
    days(day_no, name) AS (
        VALUES
          (1, '周一'),
          (2, '周二'),
          (3, '周三'),
          (4, '周四'),
          (5, '周五'),
          (6, '周六'),
          (7, '周日')
    ),

    -- 2)把你的原始过滤和分组查询,改成输出 day_no (1~7) 和 count
    raw_counts AS (
        SELECT
            -- 将 strftime('%w') (0=周日,1=周一…6=周六)
            -- 转换成 1=周一…7=周日
            ((CAST(strftime('%w', datetime(
                  substr(updated,1,4) || '-' || substr(updated,5,2) || '-' || substr(updated,7,2)
            )) AS INTEGER) + 6) % 7) + 1
            AS day_no,
            COUNT(*) AS cnt
        FROM blocks
        WHERE type = 'd'
          AND updated >= strftime(
              '%Y%m%d000000',
              datetime(
                  'now','localtime',
                  '-' || ((strftime('%w','now','localtime') + 6) % 7) || ' days'
              )
          )
          AND updated < strftime(
              '%Y%m%d000000',
              datetime(
                  'now','localtime',
                  '-' || ((strftime('%w','now','localtime') + 6) % 7) || ' days',
                  '+7 days'
              )
          )
        GROUP BY day_no
    )

-- 3)把 days 和 raw_counts 做 LEFT JOIN,COALESCE NULL 为 0,按 day_no 排序
SELECT
    d.name    AS weekday,
    COALESCE(r.cnt, 0) AS count
FROM days d
LEFT JOIN raw_counts r
  ON d.day_no = r.day_no
ORDER BY d.day_no;
统计上周更新文档

image.png

-- sort weekday, count
-- style count {margin-left: 50px;}
WITH
    -- 1)定义一周七天的表,day_no 用于排序,name 为要显示的中文星期
    days(day_no, name) AS (
        VALUES
          (1, '周一'),
          (2, '周二'),
          (3, '周三'),
          (4, '周四'),
          (5, '周五'),
          (6, '周六'),
          (7, '周日')
    ),

    -- 2)把你的原始过滤和分组查询,改成输出 day_no (1~7) 和 count
    raw_counts AS (
        SELECT
            -- 将 strftime('%w') (0=周日,1=周一…6=周六)
            -- 转换成 1=周一…7=周日
            ((CAST(strftime('%w', datetime(
                  substr(updated,1,4) || '-' || substr(updated,5,2) || '-' || substr(updated,7,2)
            )) AS INTEGER) + 6) % 7) + 1
            AS day_no,
            COUNT(*) AS cnt
        FROM blocks
        WHERE type = 'd'
          -- 起点:本周周一往前推 7 天(上周周一)00:00:00
          AND updated >= strftime(
              '%Y%m%d000000',
              datetime(
                  'now','localtime',
                  -- 先算出本周已经过了几天:((%w+6)%7) 天,再加 7 天
                  '-' || ( ((strftime('%w','now','localtime') + 6) % 7) + 7 ) || ' days'
              )
          )
          -- 终点:本周周一 00:00:00(不包含本周的数据)
          AND updated < strftime(
              '%Y%m%d000000',
              datetime(
                  'now','localtime',
                  -- 只算出本周已经过了几天:((%w+6)%7) 天
                  '-' || ( (strftime('%w','now','localtime') + 6) % 7 ) || ' days'
              )
          )
        GROUP BY day_no
    )

-- 3)把 days 和 raw_counts 做 LEFT JOIN,COALESCE NULL 为 0,按 day_no 排序
SELECT
    d.name    AS weekday,
    COALESCE(r.cnt, 0) AS count
FROM days d
LEFT JOIN raw_counts r
  ON d.day_no = r.day_no
ORDER BY d.day_no;

示例 8:生成统计图

统计本周更新文档

image.png

/* jsformat counts_csv {
    if (typeof window.echarts === 'undefined') {
        // 创建 script 元素
        const script = document.createElement('script');
        script.src = window.siyuan ? '/stage/protyle/js/echarts/echarts.min.js?v=5.3.2' : 'https://unpkg.com/echarts@5.6.0/dist/echarts.min.js';
        script.onload = function () {
            // 脚本加载完成后初始化图表
            initChart();
        };
        document.head.appendChild(script);
    } else {
        // 如果已存在,直接初始化图表
        initChart();
    }
    return 'Loading...';
    async function initChart() {
        // 基于准备好的dom,初始化echarts实例
        const span = await whenElementExist(`[data-node-id="${embedBlockID}"] .embed-counts_csv`);
        if(!span) return;
        span.style.width = '100%';
        span.style.height = '400px';
        var myChart = echarts.init(span);
        const resizeObserver = new ResizeObserver(() => {
            myChart.resize({width:span.clientWidth, height: 400});
        });
        resizeObserver.observe(span);
        const data = originFieldValue.split(',').map(item => item.trim());
        let weekday = new Date().getDay();
        weekday = weekday ? weekday - 1 : 6;
        data[weekday] = {
            value: data[weekday],
            itemStyle: {
                color: '#a90000'
            }
        };
        // 指定图表的配置项和数据
        var option = {
            tooltip: {
                trigger: 'axis',
                axisPointer: {
                    type: 'shadow'
                }
            },
            grid: {
                left: '3%',
                right: '4%',
                bottom: '3%',
                containLabel: true
            },
            xAxis: [
                {
                    type: 'category',
                    data: ['周一', '周二', '周三', '周四', '周五', '周六', '周日'],
                    axisTick: {
                        alignWithLabel: true
                    }
                }
            ],
            yAxis: [
                {
                    type: 'value'
                }
            ],
            series: [
                {
                    name: 'Direct',
                    type: 'bar',
                    barWidth: '60%',
                    data: data
                }
            ]
        };
        // 使用刚指定的配置项和数据显示图表。
        myChart.setOption(option);
    }
}*/
WITH
    days(day_no, name) AS (
        VALUES
          (1, '周一'),
          (2, '周二'),
          (3, '周三'),
          (4, '周四'),
          (5, '周五'),
          (6, '周六'),
          (7, '周日')
    ),
    raw_counts AS (
        SELECT
            ((CAST(strftime('%w', datetime(
                  substr(updated,1,4) || '-' || substr(updated,5,2) || '-' || substr(updated,7,2)
            )) AS INTEGER) + 6) % 7) + 1
            AS day_no,
            COUNT(*) AS cnt
        FROM blocks
        WHERE type = 'd'
          AND updated >= strftime(
              '%Y%m%d000000',
              datetime(
                  'now','localtime',
                  '-' || ((strftime('%w','now','localtime') + 6) % 7) || ' days'
              )
          )
          AND updated < strftime(
              '%Y%m%d000000',
              datetime(
                  'now','localtime',
                  '-' || ((strftime('%w','now','localtime') + 6) % 7) || ' days',
                  '+7 days'
              )
          )
        GROUP BY day_no
    )

SELECT
    group_concat(cnt, ',') AS counts_csv
FROM (
    SELECT
        COALESCE(r.cnt, 0) AS cnt
    FROM days d
    LEFT JOIN raw_counts r ON d.day_no = r.day_no
    ORDER BY d.day_no
);
统计上周更新文档

image.png

/* jsformat counts_csv {
    if (typeof window.echarts === 'undefined') {
        // 创建 script 元素
        const script = document.createElement('script');
        script.src = window.siyuan ? '/stage/protyle/js/echarts/echarts.min.js?v=5.3.2' : 'https://unpkg.com/echarts@5.6.0/dist/echarts.min.js';
        script.onload = function () {
            // 脚本加载完成后初始化图表
            initChart();
        };
        document.head.appendChild(script);
    } else {
        // 如果已存在,直接初始化图表
        initChart();
    }
    return 'Loading...';
    async function initChart() {
        // 基于准备好的dom,初始化echarts实例
        const span = await whenElementExist(`[data-node-id="${embedBlockID}"] .embed-counts_csv`);
        if(!span) return;
        span.style.width = '100%';
        span.style.height = '400px';
        var myChart = echarts.init(span);
        const resizeObserver = new ResizeObserver(() => {
            myChart.resize({width:span.clientWidth, height: 400});
        });
        resizeObserver.observe(span);
        const data = originFieldValue.split(',').map(item => item.trim());
        // 指定图表的配置项和数据
        var option = {
            tooltip: {
                trigger: 'axis',
                axisPointer: {
                    type: 'shadow'
                }
            },
            grid: {
                left: '3%',
                right: '4%',
                bottom: '3%',
                containLabel: true
            },
            xAxis: [
                {
                    type: 'category',
                    data: ['周一', '周二', '周三', '周四', '周五', '周六', '周日'],
                    axisTick: {
                        alignWithLabel: true
                    }
                }
            ],
            yAxis: [
                {
                    type: 'value'
                }
            ],
            series: [
                {
                    name: 'Direct',
                    type: 'bar',
                    barWidth: '60%',
                    data: data
                }
            ]
        };
        // 使用刚指定的配置项和数据显示图表。
        myChart.setOption(option);
    }
}*/
WITH
    days(day_no, name) AS (
        VALUES
          (1, '周一'),
          (2, '周二'),
          (3, '周三'),
          (4, '周四'),
          (5, '周五'),
          (6, '周六'),
          (7, '周日')
    ),
    raw_counts AS (
        SELECT
            ((CAST(strftime('%w', datetime(
                  substr(updated,1,4) || '-' || substr(updated,5,2) || '-' || substr(updated,7,2)
            )) AS INTEGER) + 6) % 7) + 1 AS day_no,
            COUNT(*) AS cnt
        FROM blocks
        WHERE type = 'd'
          -- 上周周一 00:00:00
          AND updated >= strftime(
              '%Y%m%d000000',
              datetime(
                  'now','localtime',
                  '-' || ( ((strftime('%w','now','localtime') + 6) % 7) + 7 ) || ' days'
              )
          )
          -- 本周周一 00:00:00(不含本周)
          AND updated < strftime(
              '%Y%m%d000000',
              datetime(
                  'now','localtime',
                  '-' || ((strftime('%w','now','localtime') + 6) % 7) || ' days'
              )
          )
        GROUP BY day_no
    )
SELECT
    group_concat(cnt, ',') AS counts_csv
FROM (
    SELECT
        COALESCE(r.cnt, 0) AS cnt
    FROM days d
    LEFT JOIN raw_counts r
      ON d.day_no = r.day_no
    ORDER BY d.day_no
) AS sub;

示例 9:js 指令使用示例

-- js
return await querySql(`select content, id__hide from blocks where type='d' limit 10`, errors);

-- js
const result = await querySql(`select * from blocks where type='d' limit 10`, errors);
// 仅返回content字段
return pick(result, 'content', 'id__hide');

字段名加__hide 后缀不会显示到页面中

示例 10:实现前缀文档树

参考 前缀文档树:智能文档管理新方案,新增 Tags 面板联动 的实现方式

-- js
async function main() {
    let content = await querySql(`select content from blocks where id='{{CurrDocId}}'`);
    content = content[0]?.content;
    if (content) {
        // 竖线分级功能
        let keywords = content.split('|');
        keywords = keywords.map(word => `content like '%${word}%'`);
        // 前缀匹配规则
        let prefixArr = [];
        let str = content;
        while (str.length > 0) {
            prefixArr.push(str);
            str = str.slice(0, -1); // 每次去掉最后一个字符
        }
        prefixArr = prefixArr.map(word => `content like '${word}%'`);
        // 合并SQL
        keywords = [...keywords, ...prefixArr];
        const like = keywords.join(' or ');
        let result = await querySql(`select * from blocks where type='d' and id!='{{CurrDocId}}' and (${like}) limit 500`);
        return pick(result, 'content', 'id__hide');
    }
}
return await main();

示例 11:实现相似文章

通过对标题关键词分词 +tag 实现。

-- js
// 相似文章
// api see https://api.yesapi.cn/docs-api-App.Scws.GetWords.html
const appKey = '';
// 是否使用标签筛选
const useTag = true;
async function main() {
    const doc = await querySql(`select content, tag from blocks where id='{{CurDocId}}'`);
    let content = doc[0]?.content;
    let tag = doc[0]?.tag;
    if (content) {
        // 获取分词
        const words = await fetchSyncPost('https://hn.api.yesapi.net', {s:'App.Scws.GetWords', return_data:0, yesapi_allow_origin: 1, text: content, app_key: appKey, sign:''});
        let keywords = words?.data?.words?.filter(word=>word.idf>0).map(word => word?.word) || [];
        keywords = keywords.map(word => `content like '%${word}%'`);
        // 生成like sql
        const like = keywords.join(' or ');
        // 生成tag sql
        if(tag && useTag) {
            const tags = tag.split(/\s+/)?.filter(Boolean);
            const tagLike = tags.map(tag => `tag like '%${tag}%'`).join(' or ');
            tag = tags.length > 0 ? `or (${tagLike})` : '';
        }
        let result = await querySql(`select * from blocks where type='d' and id!='{{CurDocId}}' and ((${like}) ${tag||''}) order by content limit 500`);
        return pick(result, 'content', 'id__hide');
    }
}
return await main();

示例 12:获取文档列表和其所有标题

see 文档里面的这些块是怎么组织起来的

image.png

-- js
-- style index {width: 40px;}
-- style title {width: 150px;}
// 你的SQL写这里👇
const docList = await querySql(`select * from blocks where 「👉你的查询条件写这里👈」 limit 20;`);
// 插入表格标题
docList.unshift({index: '序号', title: '文档名', headers: '标题'});
for(const i in docList) {
    if(i==0) continue; // 表格标题跳过
    const doc = docList[i];
    // 获取所有文档块
    const blocks = await fetchSyncPost("/api/block/getChildBlocks", {id: doc.id});
    // 过滤标题
    const headers = (blocks.data||[])?.filter(item=>item.type === 'h')?.map(item=>`<a href="siyuan://blocks/${item.id}">${item.subType}:${item.content}</a>`);
    docList[i].headers = headers.join(';');
    // 格式化文档标题
    docList[i].title = `<a href="siyuan://blocks/${doc.id}">${doc.content}</a>`;
    // 添加序号
    docList[i].index = i;
}
return pick(docList, 'index', 'title', 'headers');

以上仅抛转引玉, 更多功能等你发掘!

如果你有新的创意或杰作,欢迎在评论区分享!

默认格式说明

默认支持的格式化如下,比如,输入如下 SQL

select id__hide, markdown, type, subtype, created, updated from blocks where type='p' limit 3;

则默认格式如下:

  • markdown 被渲染
  • created, updated 字段被格式化为 datetime,居右显示
  • type, subtype 字段默认转换为文字描述
  • 字段之间间隔默认 10px

已知问题

  1. 由于用 js 代码片段注入实现,当刷新页面时,没办法保证代码片段一定在嵌入块被执行前执行,因此刷新时可能会出现 js 尚未生效的情况下嵌入块已经执行完了(虽然概率极低),这是嵌入块的通病,不过一般问题不大,如果有问题,只需要按 F5 刷新下即可。
  2. 暂不支持预览和导出

免责声明

本代码仅供学习交流使用,请勿用于商业用途。请严格测试后谨慎使用!使用过程中若出现任何问题,请自行承担相应责任,与代码与作者无关。

赞助作者

image.png

代码

👇 打赏后可见(为什么要打赏后可见?主要想通过这种方式统计使用人数及用户需求,以帮助作者分析哪些功能是用户最需要的)

打赏 30 积分后可见
30 积分 • 17 打赏
  • 思源笔记

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

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

    26351 引用 • 109590 回帖
  • 代码片段

    代码片段分为 CSS 与 JS 两种代码,添加在 [设置 - 外观 - 代码片段] 中,这些代码会在思源笔记加载时自动执行,用于改善笔记的样式或功能。

    用户在该标签下分享代码片段时需在帖子标题前添加 [css] [js] 用于区分代码片段类型。

    203 引用 • 1474 回帖

相关帖子

欢迎来到这里!

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

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

    这个功能能解决,书签 + 插件查询当前文档被标记的内容的时候,查询出来的内容有上下文的问题嘛?

    代码小白,问一下 F 大的 Query & View 能够实现查询当前文档被标记的部分并插入嘛?

    这是当时你回答的,但是有被标记文档的上下文,你这个代码是不是可以选择查询 MARKDOWN 文本,截取==包裹的部分,只显示被标记的文档?

    豆包给了两个方案,第一个方案能显示,但是有重复的且还是有上下文,第二个直接什么都不显示。

    -- 方案一:使用SUBSTR和INSTR函数提取高亮文本(SQLite兼容)
    SELECT 
        id,
        -- 提取第一个==和第二个==之间的文本
        SUBSTR(
            markdown,
            INSTR(markdown, '==') + 2,
            INSTR(SUBSTR(markdown, INSTR(markdown, '==') + 2), '==') - 2
        ) AS highlight_text
    FROM blocks
    WHERE root_id = '{{CurDocId}}' 
      AND markdown LIKE '%==%==%'
    ORDER BY created ASC
    LIMIT 500;
    
    -- 方案二:使用正则表达式提取所有高亮文本(需要SQLite支持REGEXP)
    SELECT 
        id,
        REGEXP_REPLACE(markdown, '.*?==([^=]+)==.*', '\1') AS highlight_text
    FROM blocks
    WHERE root_id = '{{CurDocId}}' 
      AND markdown REGEXP '==[^=]+=='
    ORDER BY created ASC
    LIMIT 500;
    
    1 回复
  • 其他回帖
  • wilsons

    0.0.6 更新说明

    1. 添加-- sort 指令
    2. 添加 -- hide 指令,废弃 view 指令(暂时还支持)
    3. 增加帮助链接

    详情见文档指令说明部分。


    附:最常用功能示例

    获取最近更新的文档

    select content, updated, id__hide from blocks where type = 'd' and trim(content) <> '' order by updated desc limit 10;
    

    image.png

    正向连接

    select * from blocks where root_id='{{CurDocId}}' and (markdown like '%((% ''%''))%' or markdown like '%((% "%"))%')
    

    历史上的今天

    SELECT * FROM blocks WHERE type = 'd'
    and substr(created, 5, 4)  = strftime('%m%d', 'now')
    and substr(created, 1, 4)  != strftime('%Y', 'now')
    and created >= strftime('%Y%m%d%H%M%S', datetime('now', '-5 years'))
    order by created desc
    

    随机漫游

    select * from blocks
    where type='d'
    order by random() limit 5;
    

    空文档(不含目录)

    SELECT a.content, a.created, a.id__hide
    FROM blocks a
    WHERE a.type = 'd'
      AND NOT EXISTS (
          -- 排除 root_id 下存在非 d 类型且 markdown 非空的情况
          SELECT 1
          FROM blocks c
          WHERE c.root_id = a.root_id
            AND c.type != 'd'
            AND c.markdown <> ''
      )
      AND NOT EXISTS (
          -- 排除当前 a.id 被其他节点的 path 包含的情况
          SELECT 1
          FROM blocks b
          WHERE b.type = 'd'
            AND b.path LIKE '%/' || a.id || '/%'
            AND b.id != a.id
      )
    order by a.updated desc
    limit 10;
    

    未命名文档

    select * from blocks where type='d' and content='未命名' order by updated desc
    

    含图片的文档

    select * from blocks where markdown like '%](assets%' order by updated desc limit 20;
    

    未完成的任务

    select * from blocks
    where type = 'i' and subtype = 't'
    and markdown like '* [ ] %'
    order by updated desc;
    

    最近引用文档

    select * from blocks where (markdown like '%((% ''%''))%' or markdown like '%((% "%"))%') and type != 'c' order by updated desc limit 20;
    

    数据库管理

    select * from blocks where type = 'av'
    

    代码块管理

    select * from blocks where markdown like '%`%`%' order by updated desc
    

    部分功能可能有所偏差,仅供参考。

    2 操作
    wilsons 在 2025-06-23 09:55:17 更新了该回帖
    wilsons 在 2025-06-23 09:54:02 更新了该回帖
  • MasterYS

    把多段查询 JS 喂给豆包重新写的代码,能查询出来,但还是含有上下文 😂 ,是书签插件返回结果只支持完整的段落块的原因,嘛?

    -- fields highlight_text,mark_order
    -- field highlight_text {display:block;margin-bottom:8px;padding:6px;border-left:2px solid #F59E0B;}
    WITH RECURSIVE highlight_extract AS (
        SELECT 
            id,
            markdown,
            INSTR(markdown, '==') AS start_pos,
            1 AS mark_order
        FROM blocks
        WHERE 
            root_id = '{{CurDocId}}' 
            AND type NOT IN ('c', 'query_embed')
            AND markdown LIKE '%==%==%'
        
        UNION ALL
      
        SELECT 
            he.id,
            SUBSTR(he.markdown, he.start_pos + 2),
            INSTR(SUBSTR(he.markdown, he.start_pos + 2), '==') + he.start_pos + 2,
            he.mark_order + 1
        FROM highlight_extract he
        WHERE INSTR(SUBSTR(he.markdown, he.start_pos + 2), '==') > 0
    ), highlighted AS (
        SELECT 
            id,
            SUBSTR(
                markdown,
                start_pos + 2,
                INSTR(
                    SUBSTR(markdown, start_pos + 2),
                    '=='
                ) - 2
            ) AS highlight_text,
            mark_order,
            created
        FROM highlight_extract
        WHERE start_pos > 0
    )
    SELECT * FROM highlighted
    ORDER BY created ASC, mark_order ASC
    LIMIT 500;
    
    
  • wilsons 12 评论

    我的目的就是通过 SQL 或者书签 + 的 SQL 提取行内只被标记的部分,不含前后文,试了最新的代码,没显示啊

    @MasterYS

    我之前给的那个代码

    /* jsformat markdown {
        //xxxxx
    }*/
    select ... from ...
    

    这个 SQL 仅在嵌入块支持,不支持书签 +,我这边可以的,如下

    image.png

    我这还是不行,不知道什么原因,用最新 061 的代码。暂时放弃了。我有个其他问题想请教下,SQL 查询到的内容想复制出来,有什么办法啊
    MasterYS
    @MasterYS 嵌入块复制不了,只能通过只读或预览模式复制文本,但复制出来文本格式比较乱。 其实还不如用 OCR 复制方便,现在有些 OCR 支持识别表格。
    wilsons
    @wilsons 明白了 有道理 可以 ocr
    MasterYS
    @MasterYS 如果量不大可以直接截图给 ai,可是免费且最强大的 ocr 了 😄 还可以按照你的要求转换,甚至百分百还原样式等也有可能 😄
    wilsons 1 赞同
    @MasterYS 0.0.6.5 支持复制了,嵌入查询右上角点,复制,可以复制查询结果,字段之间用 tab 分割,每行之间用换行分割
    wilsons
    @wilsons 收到,已更新,测试可行 强
    MasterYS
    @MasterYS 现在所有嵌入块都可以复制,格式尽可能的维持原有形式,但很难针对所有格式都无误。实现之前一直纠结,是否仅对我这个嵌入块添加复制还是所有的嵌入块,思索再三,选择了全部嵌入块。
    wilsons
    @MasterYS 更新到了 0.0.6.6 版,改进嵌入块复制查询结果,复制结果更符合真实情况
    wilsons
    @wilsons 那这是一个大增强啊,能复制出来表示能打印了,查询块以前导出是不显示的
    MasterYS
    @MasterYS 并不能打印,我没试,应该不可以,只是通过对 dom 内容拼接到剪切板而已
    wilsons
    @wilsons 可以的 我试了啊 查询到的东西复制到文档了 导出 PDF 不就相当于打印了,之前查询的东西导出 PDF 是空的
    MasterYS
    @MasterYS 这个应该是巧合和复制无关;又改进了复制功能,0.0.6.7 改进嵌入块复制查询结果时对代码块的支持等,这个根据后面碰到的问题可能会持续改进。
    wilsons
  • 查看全部回帖