前言
众所周知,思源嵌入块查询(输入{{}}弹出查询窗的那个)不支持指定字段查询,只能使用类似这样的 SQL 查询。
select * from blocks limit 3
那么,你想像下面这样查询吗?
select content, created from blocks where type='d' and trim(content) != '' limit 2
你想轻松实现这样的效果吗?
只需要下面两个 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
你 💓 心动了吗?
心动不如行动,你只需要安装下面的代码,即可立即实现上面的查询效果。
👉 代码
功能介绍
- 支持多字段查询,比如 select id, markdown, created from...
- 可指定字段样式及指定字段显示顺序
- 可以格式化常用字段数据
- 可以通过 js 代码自定义格式化字段数据
- SQL 中支持
{{CurrDocId}}
和{{CurrBlockId}}
标记,分别代表当前文档 id 和当前嵌入块 id(有时需要排除当前嵌入块时有用) - 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 格式化指令常用变量
embedBlockID 和 currBlockId 这两个变量是同一个意思,即当前嵌入块的块 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 这些参数仅在需要高级自定义返回结果时才需要,如果不清楚,忽略即可。
字段间数据共享
通过 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:生成统计
统计总数
select '总文档数:', count(*) as count from blocks where type = 'd';
统计本周更新文档
-- 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;
统计上周更新文档
-- 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:生成统计图
统计本周更新文档
/* 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 );
统计上周更新文档
/* 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();
以上仅抛转引玉, 更多功能等你发掘!
如果你有新的创意或杰作,欢迎在评论区分享!
默认格式说明
默认支持的格式化如下,比如,输入如下 SQL
select id__hide, markdown, type, subtype, created, updated from blocks where type='p' limit 3;
则默认格式如下:
- markdown 被渲染
- created, updated 字段被格式化为 datetime,居右显示
- type, subtype 字段默认转换为文字描述
- 字段之间间隔默认 10px
已知问题
- 由于用 js 代码片段注入实现,当刷新页面时,没办法保证代码片段一定在嵌入块被执行前执行,因此刷新时可能会出现 js 尚未生效的情况下嵌入块已经执行完了(虽然概率极低),这是嵌入块的通病,不过一般问题不大,如果有问题,只需要按 F5 刷新下即可。
- 暂不支持预览和导出
免责声明
本代码仅供学习交流使用,请勿用于商业用途。请严格测试后谨慎使用!使用过程中若出现任何问题,请自行承担相应责任,与代码与作者无关。
赞助作者
代码
👇 打赏后可见(为什么要打赏后可见?主要想通过这种方式统计使用人数及用户需求,以帮助作者分析哪些功能是用户最需要的)
欢迎来到这里!
我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。
注册 关于