一炮三响的解决方案

2024-04-30 更新

修正只有一个匹配项时结果为空的 bug，matchBlocks3 的 join 改为 left join。后面的代码都已修改

matchBlocks3 as ( --删除子块，保留顶层块
 select m1.*,
 case
   when m1.id_path like '%' || m2.match_id | '%' || m1.match_id then 1
   else 0
 end as hasParent from matchBlocks2 as m1 left join matchBlocks2 as m2 on m1.match_id != m2.match_id group by m1.match_id having sum(hasParent)=0
)

解决思路

解决思路

1. 查找所有符合要求的块，得到 matchBlocks

2. 建立 matchBlock 之间的层级关系

   1. 层级关系可用 父块id/子块id 拼接成 id_path 来体现，父块 id 在 id_path 左侧，子块 id 在右侧。
   2. 每个 matchBlock 的 id_path 从自己向上生成，即子块知道自己某条路径上的所有子块，父块不知道子块。
   3. 顶层块的 id_path 是自己的 id。

3. 从父块的 content 中删除其所有一级子块的 content，得到独属于父块的 content，命名为 content2

   1. 此处的父子关系是 id_path 中的父子关系，不是 blocks 表里中的父子关系
   2. 没有子块时，content2=content

4. 在 matchBlocks 中的 content2 中匹配关键词，得到 matchBlocks2

5. matchBlocks2 中可能存在父子关系，若一个块的 id 包含于另一个块的 id_path，且这个块的 id_path≠ 自己的 id，则这个块的上级块满足匹配条件，将此块从 matchBlocks2 中删除，保留其上级，得到 matchBlocksFinal

6. select * from blocks where id in (select id from matchBlocksFinal) order by yy limit n
   

‍

一、生成 matchBlocks 表

这一步比较简单，把所有的条件都填上就行。

with matchBlocks as (
  select * from blocks where content like '%keyword1%' and content like '%keyword2%' and blah, blah, blah
)

P.S. 在这一步不能进行 order by yy limit n 结果排序、数量限制等操作，应该放到最后一步。

二、建立 matchBlock 之间的层级关系

需要使用递归查询，通过 id、parent_id 来获取块之间的父子关系，得到 id_path。

从逻辑上来说向下构建层级关系会比向上更容易理解，比如向下层级关系的总体思路为：

1. 查找所有符合要求的块，得到 matchBlocks 表

2. 建立 matchBlock 之间的层级关系

   1. 层级关系可用 父块id,子块id 拼接成 id_path 来体现，父块 id 在 id_path 左侧，子块 id 在右侧。
   2. 每个 matchBlock 的 id_path 从自己向下生成，即父块知道自己某条路径上的所有子块，子块不知道父块。
   3. 底层块的 id_path 是自己的 id。

3. 从父块的 content 中删除其所有一级子块的 content，得到独属于父块的 content，命名为 content2

   1. 此处的父子关系是 id_path 中的父子关系，不是 blocks 表里中的父子关系
   2. 没有子块时，content2=content

4. 在 matchBlocks 中的 content2 中匹配关键词，得到 matchBlocks2 表

5. matchBlocks2 中可能存在父子关系，若一个块的 id_path 包含于另一个块的 id_path，则此块是子块，将其从 matchBlocks2 中删除，得到 matchBlocks3

6. select * from blocks where id in (select id from matchBlocks3) order by yy limit n
   

步骤 5 相较于开头的思路更容易理解一些，但是向下构建会存在一些明显的问题。

注意

上图中展示的是每个块的 content，在编辑器中不一定会显示出来。

例如，块 2 是容器块，它的 content 由其所有一级子块的 content 拼接而成，在思源的编辑器中其本身是不显示的，显示的是其内部的子块，只能通过块标找到它。

块 1、3 是标题块，不是容器块，其 content 不会包含子块的 content，在编辑器中会显示。

以上图为例，搜索思源、笔记两个关键词会匹配到块 1.2、2、2.1 和 2.2。

以这些块为起点向下建立层级关系，一个块可能得到多个 id_path，保存哪一个？还是全部保存？这是个麻烦的问题，选择困难症犯了。

• 1.2→1.2.1，id_path=1.2
• 2→2.1→2.1.1，id_path=2/2.1
• 2→2.2→2.2.1，id_path=2/2.2
• 2→2.2→2.2.2，id_path=2/2.2
• 2→2.2→2.2.3，id_path=2/2.2
• 2→2.3，id_path=2
• 2→2.4，id_path=2
• 2.1→2.1.1，id_path=2.1
• 2.2→2.2.1，id_path=2.2
• 2.2→2.2.2，id_path=2.2
• 2.2→2.2.3，id_path=2.2

若向上建立层级，每个块只有唯一的 id_path，虽然后面使用起来需要多动点脑子，但能走通。

• 1.2→1→ 文档块，id_path=1.2
• 2.1→2→ 文档块，id_path=2/2.1
• 2.2→2→ 文档块，id_path=2/2.2
• 2→ 文档块，id_path=2

with recursive hierarchyUp(id,parent_id,match_id,id_path,type,content) as ( --建立层级关系，得到id_path，包含了局部id_path
   select id,parent_id,id,id,type,content from matchBlocks
   union
   select 
     b.id,b.parent_id,h.match_id,
     case
       when b.id in (select id from matchBlocks) then b.id || '/' || h.id_path --当b属于matchBlocks时，将其id加入id_path
       else h.id_path
     end,
     b.type,h.content
   from blocks as b join hierarchyUp as h on b.id = h.parent_id
)

三、最大块 or 最小块？

为避免搜索结果重复，需要先讨论最大块和最小块的问题。

块 2、2.1 和 2.2 都是匹配到的块，其中块 2 是容器块，显示块 2 时也会同时显示块 2.1 和 2.2，则块 2 可以称之为最大块，即内容最多的块；而块 2.1、2.2 之间的内容不包含块 2 的内容，它们是最底层的、内容最少的块，可以称之为最小块。

有两种显示方式，一种是显示最大块——块 2，优点是条目少、内容全，缺点是显示内容太多，需要在块内继续查找目标；另一种是显示最小块——块 2.1 和 2.2，优点是定位精确，很容易找到搜索的内容。

一般情况下，显示最小块比较合适。但可能会漏掉一些满足关键字的内容，比如块 2.3 包含了思源，块 2.4 包含了笔记，按理说也需要展示这部分内容，此时应该显示最大块。

一种可行的解决方案是从父块 content 中删除所有一级子块的 content，即从块 2 中删除块 2.1 和 2.2 的 content，保留其独享的 content 作为 content2（思源笔记 123），如果 content2 也能匹配关键字，则应该显示最大块，否则显示最小块。

这个解决方案有一个超出我 SQL 水平的问题：父块的一级子块的数量是不确定的，如何解决这种动态数量问题，摸不着头脑。拷问 AI 半天也没有得到解决，只会让我用 python🦙

此外，从一个字符串中删除多个子字符串还存在一些小问题：

1. 每个子字符串只能删除一次。sqlite 的 repalce 函数会把字符串中的所有子字符串全部替换掉，例如从块 2 思源笔记，英文名SiYuan，是DVd一家子出品的一款本地优优先的笔记软件。思源笔记思源笔记123 依次 replace 块 2.1 和块 2.2 为空：

   1. replace 块 2.1 思源笔记，英文名SiYuan，是DVd一家子出品的一款本地优先的笔记软件。 为空，content2=思源笔记思源笔记123
   2. replace 块 2.2 思源笔记，content2=123
   3. 此时 123 无法匹配思源、笔记两个关键词，与预期不符，原因在于上一步把块 2.3 和块 2.4 组成的 思源笔记123 中的思源笔记也替换了。

2. 子字符串的删除顺序对结果有影响。假设把删除顺序改为先删除块 2.2，再删除块 2.1，且假定采用了一些技巧每次只删除一次：

   1. 删除块 2.2 思源笔记，content2=，英文名SiYuan，是DVd一家子出品的一款本地优先的笔记软件。思源笔记思源笔记123
   2. 删除块 2.1 思源笔记，英文名SiYuan，是DVd一家子出品的一款本地优先的笔记软件。，content2 中已经不包含块 2.1 的内容了！删除失败！

正则表达式，永远的神，吗？

遭受了上述毒打之后，终于想起了我们可爱又可恨的万金油朋友——正则表达式！

上述问题可以转化为：在 matchBlocks 的 content 中查找同时匹配所有一级子块 content 和所有搜索关键词的块，脑海中第一个浮现 （AI 脱口而出） 如下正则表达式：

(?=.*child_content1)(?=.*child_content2)(?=.*child_contentn)(?=.*keyword1)(?=.*keyword2)(?=.*keywordn)

历史告诉我们，前进的道路是曲折的。经过测试这个表达式不行，它表示每个子字符串至少出现 1 次，然而 child_content 中是肯定包含 keyword 的，实际上等效为下面的表达式：

(?=.*child_content1)(?=.*child_content2)(?=.*child_contentn)

用膝盖想也知道父块必然满足这个正则表达式啊！毕竟 content=child_content1+child_content2+...+child_contentn

正则表达式，卒。

问题的本质

要找到包含所有 keyword 的 content，关键在于 keyword 出现的次数！

只要父块 content 中 keyword 出现的次数比所有一级子块 content 中 keyword 出现的次数之和多就行！

解决方案改为：

1. 获取各个关键词在所有 matchBlock 中出现的次数，结果保存为 keyword 和 occurrences
2. 判断各个关键词在父块中出现的次数是否比子块多，结果保存为 matched，1 表示更多，0 表示相等或更少，底层块直接设置为 1.
3. 判断父块的所有关键词匹配次数是否都比子块多，判断结果保存为 matched2，从 matchBlocks 中删除 matched2=0 的块，得到 matchBlocks2。

with 
hierarchyUpDoc as ( --筛选最长的id_path，即到文档块的id_path，并得到各关键词在content中出现的次数
  select *, 'keyword1' as keyword, 
     (LENGTH(content) - LENGTH(REPLACE(content, 'keyword1', ''))) / LENGTH('keyword1')  AS occurrences   --替换后减少的长度/子串长度=子串出现次数
  from hierarchyUp where type = 'd'
  union --联接多个关键词的结果
  select *, 'keyword2' as keyword, 
     (LENGTH(content) - LENGTH(REPLACE(content, 'keyword2', ''))) / LENGTH('keyword2')  AS occurrences   
  from hierarchyUp where type = 'd'
),
matchBlocksKeyword as (  --各个关键词在父块与子块中出现的次数比较
  select h1.*,
  case
    when h2.match_id is null then --没有子块
      1 
    else
      h1.occurrences>sum(h2.occurrences) --关键词在父块中出现的次数比子块多
	end  as matched
  from hierarchyUpDoc as h1 left join hierarchyUpDoc as h2 on h2.id_path like '%' || h1.match_id || '/' || h2.match_id and h1.keyword=h2.keyword group by h1.match_id,h1.keyword
),
matchBlocks2 as (  --保留所有关键词匹配次数都比子块多的块
  select match_id,id_path,min(matched) as matched2 from matchBlocksKeyword group by match_id having matched2>0
)

最后一公里

再放一次这张图。经过上面一顿折腾，块 2、2.1 和 2.2 在 matchBlocks2 中依然存活：

根据最大块、最小块一节的讨论，此时应该保留块 2。大致步骤如下：

1. 根据 id_path 判断块 2 是否是块 1 的父块、爷块、曾爷爷块、哪里凉快，如果是，在块 1 中保存 hasParent=1，否则=0
2. 从 matchBlocks2 获取 sum(hasParent)=0 的块，也就是每个路径上的最大块，得到 matchBlocks3

with matchBlocks3 as ( --删除子块，保留顶层块
 select m1.*,
 case
   when m1.id_path like '%' || m2.match_id || '%' || m1.match_id then 1
   else 0
 end as hasParent from matchBlocks2 as m1 left join matchBlocks2 as m2 on m1.match_id != m2.match_id group by m1.match_id having sum(hasParent)=0
)

然后就没有然后了，一句话完事。

select * from blocks where id in (select match_id from matchBlocks3) ordery by yy limit n

千呼万唤始出来

弄了两天都在走弯路，还好弄出来了，时间没有白费。最终的 SQL 如下：

with recursive 
matchBlocks as (
  select * from blocks where content like '%keyword1%' and content like '%keyword2%'
),
hierarchyUp(id,parent_id,match_id,id_path,type,content) as ( --建立层级关系，得到id_path，包含了局部id_path
   select id,parent_id,id,id,type,content from matchBlocks
   union
   select 
     b.id,b.parent_id,h.match_id,
     case
       when b.id in (select id from matchBlocks) then b.id || '/' || h.id_path --当b属于matchBlocks时，将其id加入id_path
       else h.id_path
     end,
     b.type,h.content
   from blocks as b join hierarchyUp as h on b.id = h.parent_id
),
hierarchyUpDoc as ( --筛选最长的id_path，即到文档块的id_path，并得到各关键词在content中出现的次数
  select *, 'keyword1' as keyword, 
     (LENGTH(content) - LENGTH(REPLACE(content, 'keyword1', ''))) / LENGTH('keyword1')  AS occurrences   --替换后减少的长度/子串长度=子串出现次数
  from hierarchyUp where type = 'd'
  union --联接多个关键词的结果
  select *, 'keyword2' as keyword, 
     (LENGTH(content) - LENGTH(REPLACE(content, 'keyword2', ''))) / LENGTH('keyword2')  AS occurrences   
  from hierarchyUp where type = 'd'
),
matchBlocksKeyword as (  --各个关键词在父块与子块中出现的次数比较
  select h1.*,
  case
    when h2.match_id is null then --没有子块
      1 
    else
      h1.occurrences>sum(h2.occurrences) --关键词在父块中出现的次数比子块多
	end  as matched
  from hierarchyUpDoc as h1 left join hierarchyUpDoc as h2 on h2.id_path like '%' || h1.match_id || '/' || h2.match_id and h1.keyword=h2.keyword group by h1.match_id,h1.keyword
),
matchBlocks2 as (  --所有关键词在父块中出现的次数都比子块多
  select match_id,id_path,min(matched) as matched2 from matchBlocksKeyword group by match_id having matched2>0
),
matchBlocks3 as ( --删除子块，保留顶层块
 select m1.*,
 case
   when m1.id_path like '%' || m2.match_id | '%' || m1.match_id then 1
   else 0
 end as hasParent from matchBlocks2 as m1 left join matchBlocks2 as m2 on m1.match_id != m2.match_id group by m1.match_id having sum(hasParent)=0
)
select * from blocks where id in (select match_id from matchBlocks3) ordery by yy limit n

测试工具

做了个 quicker 动作用来测试。打开思源笔记后运行此动作，输入需要搜索的关键词，确认后会自动在文档插入一个嵌入块。

一炮三响解决方案测试 - by 浅沧 - 动作信息 - Quicker (getquicker.net)

标题块何去何从

留一个小尾巴。上面的讨论更多的是针对容器块，对于文档块和标题块这两个也能成为父块的块类型未涉及。

如下图，搜索思源，初步匹配到块 1 和 1.2，块 1 中思源出现的次数不比子块 1.2 多，所以只保留子块作为搜索结果。但是标题在文章中作为部分内容的总结，你说应该优先展示标题块，这也不是没有道理，我个人是赞成还是展示最小块的方案，不要对标题块进行特殊处理。

文档块就比较简单了，主要看使用场景来：

• 搜索面板，文档标题能够匹配所有关键词的话肯定要显示的！没的说！
• 嵌入块，此处还是保留最小块原则，优先显示文档内部的块。

在思源中的实现

目前思源的搜索框出来的结果还存在一炮三响的问题，如下图。

@88250 本方案可以很方便地在思源的搜索框中实现来解决一炮三响问题。

实施步骤：

1. 将搜索框中的关键词通过空格拆分成 keywords 列表

2. 根据 keywords 列表生成两个字符串 str1 和 str2。

   1. str2

      string str2 = keywords.Select(
      	keyword => $"select *, '{keyword}' as keyword, (LENGTH(content) - LENGTH(REPLACE(content, '{keyword}', ''))) / LENGTH('{keyword}')  AS occurrences  from hierarchyUp where type = 'd'"
      	).ToList().JoinToString(Environment.NewLine+"union"+Environment.NewLine);
      

   2. str1

      string str1 = keywords.Select(
      	keyword => $"content like '%{keyword}%'"
      	).ToList().JoinToString(" and ");
      

3. 将上面两个字符串分别塞到如下两个位置即可。str1 需要根据搜索框的其他设置项增加相应的条件，如块类型过滤。

4. 搜索框好像没有下图红框中相关设置，可以直接删除。

5. 其他部分不需要动，可以直接用。