samedi 17 octobre 2015

How to phrase query a SQLite FTS table using prefix?

I'm in a situation where i need to do a FTS query which matches on multiple fields, but for some reason when doing a query looking for a "phrase" using the prefix i'm getting extra results because it's not matching the whole query.

For instance:

SELECT favorite,cd_title, content, is_child, parent FROM blazing WHERE blazing MATCH 'cd_title:Industry - Sport - Pop 1'

Will return 141 results on my query, while

SELECT favorite,cd_title, content, is_child, parent FROM blazing WHERE cd_title MATCH 'Industry - Sport - Pop 1'

Will fetch ( precisely ) the 58 records i need.

This wouldn't be a problem if i didn't have to deal with multiple fields while searching.

A workaround i found is:

SELECT favorite, content, is_child, parent FROM blazing WHERE ROWID IN ( SELECT ROWID from blazing WHERE cd_title MATCH "Industry - Sport - Pop 1" )

but that would mean having to do multiple queries and INTERSECT them in order to do a query matching multiple fields.

I tried using single quotes and

Aucun commentaire:

Enregistrer un commentaire