查询某一个子文档下面不含标签的文档
see https://ld246.com/article/1723590423117
SELECT * FROM blocks where type = 'd' and path like '%20240725203634-j3t72jv/%' and tag = '' and id not in (
SELECT root_id FROM blocks where type <> 'd' and path like '%20240725203634-j3t72jv/%' and tag <> ''
) ORDER BY random() LIMIT 2
查询包含指定标签的文档
see https://ld246.com/article/1734338710962
SELECT * FROM blocks where type = 'd' and tag like '%#demo2#%' or id in (
SELECT root_id FROM blocks where type <> 'd' and tag like '%#demo2#%'
) ORDER BY created desc;
查询网络图片或网络链接
SELECT * FROM blocks WHERE type not in ('c', 'query_embed') and markdown like '%%'
-- 如果链接格式可以用not排除,比如
SELECT * FROM blocks WHERE type not in ('c', 'query_embed') and markdown like '%[%](http%)%' and markdown not like '%%'
-- 或
-- 思源不支持正则
-- SELECT * FROM blocks WHERE type not in ('c', 'query_embed') and markdown REGEXP '!\[.*?\]\(https?:\/\/[^\)]*\)';
查询父文档下所有没有任何内容的子文档
```sql
select * from blocks where path like '%/20250128095334-2lz30ur%' and type = 'd' and root_id not in (
select distinct root_id from blocks where path like '%/20250128095334-2lz30ur%' and type != 'd' and content != ''
)
```
如果不包含父文档本身,可以sql最后添加 and root_id != '20250128095334-2lz30ur'
查询包含指定关键词的链接
SELECT *
FROM blocks
WHERE markdown LIKE '%[%关键词%](%)%' and type REGEXP '^[htp]$';
查询所有链接
SELECT *
FROM blocks
WHERE markdown LIKE '%[%](%)%';
查询包含图片的块
select * from blocks where markdown like '%](assets%' order by updated desc limit 20;
查询所有引用
select * from blocks where (markdown like '%((% ''%''))%' or markdown like '%((% "%"))%') and type != 'c' order by updated desc limit 20;
显示往年今昔日记
显示日记文档(这个好像没意义,因为推算都知道去年进入是哪个日记,除非你的日记名不是按日期命名)
注意,这个可能获取所有笔记的日记,如果仅某个笔记下,请修改 ial 为 path,参考下面个 SQL 修改。
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'))
and ial like '%custom-dailynote-%'
order by created desc
显示日记块内容(注意,这个根据楼主的日记路径写的,其他源友需要更改 path 路径)
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'))
and hpath like '%/日记%'
order by created desc
