postgres查询按AND部分匹配排序，然后按OR匹配排序

Question

所以我有一个places的表。每个地方可以有多个posts。每个帖子可以有多个tags。并且每个CCD_ 4和CCD_。因此：

places
id
posts
place_id
tags
id
name
tags_map
post_id
tag_id

我正在尝试编写一个排名/搜索查询，例如，如果有人搜索标签a和b，那么他们将获得所有用a和b标记的地方的结果，然后是用a或b标记的地方。

如果一个地方有2个与之相关的帖子，那么它就有所有这些帖子的标签。我正在寻找部分匹配——我一直在尝试使用SIMILAR TO %(a|b)%，但这只会给我OR结果。

排名应如下：

如果一个地方同时具有这两个标签，则它的排名将高于具有或的地方。如果他们同时拥有这两个标签，那么总匹配标签越多的标签排名就越高。在查询的OR部分，根据哪个地方有更多匹配标签来对它们进行排名。

我很难理解所需的必要JOIN，以及如何聚合和优先排序and结果和OR结果

这可行吗？

EDIT：示例

Places: a, b, c
Posts: a1, a2, b1, b2, c1
Tags: 
* a1_wand, a1_ball
* a1_wander
* b1_baller
* b2_wand
* c1_kaballer, c1_bababall

如果你搜索wand和ball，你会首先得到与球和魔杖部分匹配的，所以放置a和b，因为a有更多的匹配(2个用于魔杖，1个用于球(，所以它会在b之前排序(CCD_19有1个用于球体和1个用于魔杖(。而c只匹配其中一个查询术语(两次，但仍然只匹配其中的一个术语(，所以接下来就是。

如果你只搜索ball，那么你会先发布c，因为它有2个匹配项，然后a和b都有一个匹配项所以它们只在created_at日期或其他时间订购。

Answer 1

如果我理解正确的话，这只是将所有表连接在一起，过滤您想要的标签，并聚合以进行计数：

select pl.*, array_agg(distinct t.name) as tags,
count(*) as num_matches
from places pl join
posts p
on pl.place_id = p.place_id join
tags_map tm
on tm.post_id = p.post_id join
tags t
on t.tag_id = tm.tag_id
where t.name in ('a', 'b')
group by pl.place_id
order by count(distinct t.name) desc,
count(*) desc;

编辑：

对于部分匹配，它将类似于：

select pl.*, array_agg(distinct t.name) as tags,
count(*) as num_matches
from places pl join
posts p
on pl.place_id = p.place_id join
tags_map tm
on tm.post_id = p.post_id join
tags t
on t.tag_id = tm.tag_id
where t.name ~ 'a|b'
group by pl.place_id
order by (max(t.name ~ 'a')::int) + (max(t.name ~ 'b')::int) desc,
count(*) desc;