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