9

I am using FTS for querying my database for increasing the search speed as I need to search in text description also,

When I am trying to query using single column its working fine like below

select * from productsearch where productsearch match ('prod_name:panasonic*tw*')

And also,

select * from productsearch where productsearch match ('prod_short_desc:samsung*s5*')

So, above both queries give me expected result but when I try to combine both queries using OR operator its not giving me any result

select * from productsearch where productsearch match ('prod_name:panasonic*tw* OR
                                                         prod_short_desc:samsung*s5*')

So, I want to know if I am doing anything wrong in here while using OR operator for searching multiple columns

UPDATE

Below query is working fine but that doesn't fulfill my requirement,

select * from productsearch where productsearch match ('prod_name:panasonic* OR 
                                                            prod_short_desc:samsung*')

You can see that if I remove the multiple tokens then its working fine with OR operator too.

Lalit Poptani
  • 67,150
  • 23
  • 161
  • 242
  • I know nothing about FTS, but... could you try: `match ('prod_name:panasonic*tw*' OR 'prod_short_desc:samsung*s5*')` (separated strings in the same match clause) **or** `match ('prod_name:panasonic*tw*') OR match('prod_short_desc:samsung*s5*')` (separated match clauses)? – Phantômaxx Sep 24 '14 at 08:46
  • @FrankN.Stein tried both, doesn't work 1st one give all result it means it completed skips the conditions & 2nd one gives wrong paramters to function match() – Lalit Poptani Sep 24 '14 at 08:55
  • Well, just a trial... I thougth it would work like a normal clause (say like when you want to check if a month is even and a day is odd). – Phantômaxx Sep 24 '14 at 09:00

2 Answers2

1

SQLite's FTS supports only simple prefix searches.

A query like prod_short_desc:samsung*s5* consists of two expressions, prod_short_desc:samsung* and s5*, which behave exactly the same as if you had written prod_short_desc:samsung* s5*.

If you have compiled SQLite for the enhanced query syntax, you could use a query like:

prod_short_desc:samsung* prod_short_desc:s5* OR prod_name:panasonic* prod_name:tw*

If you have compiled SQLite for the standard query syntax, you cannot use a single query for this because the OR operator's precedence is too high and cannot be modified with parentheses.

CL.
  • 173,858
  • 17
  • 217
  • 259
1

So, I found the solution finally,

instead of searching from all the columns individually, I created a single column in the database which contains data for required columns to be searched into,

Example

I need to search in prod_name & prod_short_desc columns, so I created a column named data in database and appended the values of prod_name & prod_short_desc then looking up into only data field worked like a charm

prod_name | prod_short_desc

samsung  | samsung s5

So, now I merged the data of both the columns into one with space as a seperator

data

samsung samsung s5

And then search was very fast indeed with below query,

select * from productsearch where productsearch match ('samsung*s5*')
Lalit Poptani
  • 67,150
  • 23
  • 161
  • 242