根据最长匹配组织搜索结果

Question

I have a database table that contains project keywords. Each project has several keywords.

In the web application, a user can perform a search by entering multiple keywords to search for. If the user enters (for example) four keywords, I want to return the search result as a list of projects. The projects should be ordered based on the longest matches first. The first projects listed will the ones that matched on all four keywords, followed by projects that matched on three out of four keywords, etc.

What query can I write to return the results?

This is a rough idea of what I think the query would look like:

select projectID 
  from project_keyword 
 where keyword = '*keyword1*' 
    or keyword = '*keyword2*'
    or keyword = '*keyword3*'

But what I want is any projectID that matches all three keywords to be displayed first (if any), followed by the projectID that matched two out of three keywords, etc. projectID that don't have any matching keywords are not returned.

Example:

projectID  keyword 
---------  -------
 456       salsa
 456       guacamole
 456       tamale
 511       salsa
 511       tamale
 511       burrito
 511       taco
 654       margarita
 654       nachos
 789       margarita
 789       salsa  
 789       taco

For user keyword search for 'salsa','tamale','burrito', the query should return these projectID, in this order.

projectID
---------
 511          (matches all three)
 456          (matches two)
 789          (matches one)

How do I get the rows from the query returned in the specified order? (What query can I write to get that.)

Answer 1

Something like this should work:

 SELECT k.projectID
   FROM project_keyword k
  WHERE k.keyword IN ('*keyword1*','*keyword2*','*keyword3*')
  GROUP BY k.projectID
  ORDER BY COUNT(DISTINCT k.keyword) DESC, k.projectID

(This assumes that the table contains multiple rows for a projectID, one row for each keyword. If that's not how the table is organized, if it's a single row, and you are looking for matching keywords that occur somewhere in a column, we'd need a different query.)

If you want to return the list of keywords that matched, you could use a GROUP_CONCAT aggregate in the SELECT list

 SELECT k.projectID
      , GROUP_CONCAT(DISTINCT k.keyword ORDER BY k.keyword) AS keywords_matched
   FROM ...