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.)
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 ...