How to order results based on number of search term matches?

Question

I am using the following InnoDB tables in mysql to describe records that can have multiple searchtags associated with them:

TABLE records
    ID
    title
    desc

TABLE searchTags
    ID
    name

TABLE recordSearchTags
    recordID
    searchTagID

To SELECT records based on arbitrary search input, I have a statement that looks sort of like this:

SELECT 

    recordSearchTags.recordID

FROM 

    recordSearchTags

LEFT JOIN searchTags 

ON recordSearchTags.searchTagID = searchTags.ID 

WHERE 

    searchTags.name LIKE CONCAT('%','$search1','%') OR
    searchTags.name LIKE CONCAT('%','$search2','%') OR
    searchTags.name LIKE CONCAT('%','$search3','%') OR
    searchTags.name LIKE CONCAT('%','$search4','%');

I'd like to ORDER this resultset, so that rows that match with more search terms are displayed in front of rows that match with fewer search terms.

For example, if a row matches all 4 search terms, it will be top of the list. A row that matches only 2 search terms will be somewhere in the middle. And a row that matches just one search term will be at the end.

Any suggestions on what is the best way to do this?

Thanks!

Answer 1

* Replaced answer, since fulltext isn't an option

Alright, it's not pretty, but you should be able to do something like this:

ORDER BY (searchTags.name LIKE CONCAT('%','$search1','%') 
          + searchTags.name LIKE CONCAT('%','$search2','%')
          + searchTags.name LIKE CONCAT('%','$search3','%')
          + searchTags.name LIKE CONCAT('%','$search4','%'))
DESC;

LIKE returns 1 on a match or 0 if there is no match, so you should just be able to add the results together.

Answer 2

This isn't very pretty but one way would be to union the 4 likes in 4 statements like

select ... where  searchTags.name LIKE CONCAT('%','$search1','%')
union 
select ...

and so on. Wrap that in a:

select recordSearchTags.recordID, count(*) from (<inner unions>)
group by recordSearchTags.recordID 
order by count(*)