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
sql
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;
查询网络图片或网络链接
sql
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?:\/\/[^\)]*\)';
查询父文档下所有没有任何内容的子文档
js
```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'