dory4404
dory4404
2015-07-16 05:42

pdo按相关性排序,然后按另一个字段排序

已采纳

I have a query that selects from my database and then orders by relevance:

select * from `table` where MATCH (`name`) AGAINST (:search IN BOOLEAN MODE) order by MATCH (`name`) AGAINST (:search2 IN BOOLEAN MODE) desc LIMIT :start, :limit

When these results are shown I want the user to have an option of filtering by price. How can I do this when ORDER BY is already used for the relevance?

Can I create a view of the results and then query the view to filter by price?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • duanboxue3422 duanboxue3422 6年前

    you can Order by as many columns as you want. Syntax is:

    ORDER by column1 desc, column2 asc, column3 desc ...
    

    So you might want to have:

    select * from `table` where MATCH (`name`) AGAINST (:search IN BOOLEAN MODE) order by MATCH (`name`) AGAINST (:search2 IN BOOLEAN MODE) desc, `price` asc LIMIT :start, :limit
    

    If you want a price Range you have to build it into an WHERE clause

    select * from `table` where MATCH (`name`) AGAINST (:search IN BOOLEAN MODE) AND `price` >= 5 AND `price` < 10 order by MATCH (`name`) AGAINST (:search2 IN BOOLEAN MODE) desc, `price` asc LIMIT :start, :limit
    
    点赞 评论 复制链接分享