Search code examples
androidsqlitefull-text-searchfts4

Searching in multiple columns using Full Text Search(FTS) with multiple tokens using OR operator


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.


Solution

  • 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*')