MySQL全文布尔搜索-按相关性和另外一个字段排序

问题描述

所以基本上我有两个表.第一张称为照片"的表格如下所示:

So basically I have two tables. First table called 'photos' looks like this:

id title
1  Some text here
2  Another text here

第二个名为"likes"的表如下所示:

and the second table called 'likes' looks like this

id photos_id user_id
1  1         10
2  1         11
3  2         12

photos_id对应于照片"表中的ID

photos_id corresponds to id from 'photos' table

我正在使用以下查询进行全文布尔搜索，然后按相关性排序

I am using the following query to do fulltext boolean search and then sort by relevance

SELECT *, MATCH (title) AGAINST ('text' in boolean mode)
AS score FROM photos
WHERE MATCH (title) AGAINST ('text' in boolean mode) order by score desc;

按相关性排序很有效，但是现在我想按相关性相等时的喜欢总数进行排序...类似"按分数排序，total_likes desc ".有帮助吗?

Sorting by relevance is working well but now I want to sort also by total number of likes when the relevance is equal ... something like "order by score, total_likes desc". Any help?

推荐答案

您可以为此进行Left Join并检查 SQLFiddle 此处.

You can do Left Join for it and check SQLFiddle here.

SELECT
  p.*,
       MATCH (title) AGAINST ('text' IN BOOLEAN MODE) AS score ,
       COUNT(li.id) AS total_likes 
FROM photos p
  LEFT JOIN likes li
    ON p.id = li.photo_id
     WHERE  MATCH (p.title) AGAINST ('text' IN BOOLEAN MODE) 
    GROUP BY 
      li.photo_id

ORDER BY 
      score , total_likes DESC

这篇关于MySQL全文布尔搜索-按相关性和另外一个字段排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！