3

My simple SQLite FTS3 implementation currently matches user queries with stored results and returns them as long as these results contain all the words in the user query (in other words, the FTS table was created using a simple tokenizer).

Now, I want to make the search more intelligent in that, I want it to rank/order results based on the number of words in the query that match results. For instance,

SELECT name FROM nametable WHERE name MATCH 'fast food restaurant'

which currently returns 

SUBWAY FAST FOOD RESTAURANT
MCDONALDS FAST FOOD RESTAURANT

should return

SUBWAY FAST FOOD RESTAURANT
MCDONALDS FAST FOOD RESTAURANT
CHIPOTLE FAST FOOD
PIZZA FAST FOOD
GOOD FOOD OUTLET

in that order. In other words, the search output should not be restricted to results which necessarily contain all words of user queries, rather, place results that contain all words higher up in the list while making room for results that return a partial match.

One simple way of doing this is to run multiple queries using all permutations of the user's input and UNION the results in the required order, but that would be rather inefficient.

gc20
  • 61
  • 1
  • 2
  • 5

2 Answers2

7

More easily just do:

SELECT name FROM nametable WHERE name MATCH 'fast OR food OR restaurant'

No need to handle multiple queries.

Frederic Yesid Peña Sánchez
  • 2,661
  • 23
  • 21
2

Here is an untested idea.

SELECT name
FROM (
      SELECT name, 1 as matched
      FROM nametable
      WHERE name MATCH 'fast'
    UNION ALL
      SELECT name, 1 as matched
      FROM nametable
      WHERE name MATCH 'food'
    UNION ALL
      SELECT name, 1 as matched
      FROM nametable
      WHERE name MATCH 'restaurant'
  )
GROUP BY name
ORDER BY SUM(matched) DESC, name
btilly
  • 43,296
  • 3
  • 59
  • 88
  • That works as desired. Smarter solution than my originally suggested method of conducting a union of all permutations. Unless there's a more native solution, I guess your answer best meets my needs. Thanks. – gc20 Jul 04 '11 at 03:33