sort by relevance and one more field

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

id title
1  Some text here
2  Another text here

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 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;

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 。

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

链接地址: http://www.djcxy.com/p/75290.html

上一篇: 全文搜索引擎，多列，布尔模式

下一篇: 根据相关性和一个领域进行排序