Sqlite3 Order By relevance to the input

March 22, 2022

    c = conn.cursor()
    search = "SELECT * FROM chavacanowords WHERE filipinoword = ? OR filipinoword = ? OR filipinoword LIKE ? OR filipinoword LIKE ?"
    c.execute(search, ('%'+i+'%', '%'+i.title()+'%', '%'+i+'%', '%'+i.title()+'%'))
    result = c.fetchone()
    if result:
      translatedwords.append(result[0])
    else:
      translatedwords.append(i)

I’m trying to match the words input by the user. However, the c.fecthone() gets the earliest entry in on the database.

User Input: Sara

Database: (Sabroso, Sarap) (Trangka, Sara)

Translation: Sabroso (since Sabroso was on the database first than the Trangka)

I’m trying to filter out all of the exact entries by "filipinoword = ? OR filipinoword = ?" and trying to search for resemblance if the exact match doesn’t exist "filipinoword LIKE ? or filipinoword LIKE ?"

How can I do it in a way where the exact match comes first and if none then proceed to the LIKE part?

>Solution :

You can modify your query to use a UNION of two queries, with the first looking for an exact match and the second an inexact one. A flag is used to determine whether the match is exact or not and the results sorted by that flag. For example:

SELECT translation, filipinoword
FROM (
  SELECT *, 1 AS exactmatch
  FROM chavacanowords 
  WHERE filipinoword = 'sara' OR filipinoword = 'Sara'
  UNION ALL
  SELECT *, 0 AS exactmatch
  FROM chavacanowords 
  WHERE filipinoword LIKE '%sara%' OR filipinoword LIKE '%Sara%'
) m
ORDER BY exactmatch DESC
LIMIT 1

Demo on db-fiddle

The sorting ensures that if an exact match is present, it is the value returned.

For python purposes you would replace the strings in the WHERE clause with ? and pass them as parameters i.e.

search = "SELECT translation, filipinoword \
FROM ( \
  SELECT *, 1 AS exactmatch \
  FROM chavacanowords \
  WHERE filipinoword = ? OR filipinoword = ? \
  UNION ALL \
  SELECT *, 0 AS exactmatch \
  FROM chavacanowords \
  WHERE filipinoword LIKE ? OR filipinoword LIKE ? \
) m \
ORDER BY exactmatch DESC \
LIMIT 1"
c.execute(search, (i, i.title(), '%'+i+'%', '%'+i.title()+'%'))