CODEWITHSUNDEEP
androidsqlitefull-text-search
tdmsoares
tdmsoares

Reputation: 533

Using FTS3/4 Table based on Select Statements from Normal Table

In my app, I'm developing in Android, I have a Sqlite Table called Transactions, with these fields:

_Id | Date | Value | Notes

I already have a ListView showing results filtering by Date (for example):

Select * FROM Transacions WHERE Date BETWEEN '2016-04-25' AND '2016-05-14'

It works fine, but I want to implement a SearchView to search transactions between a custom date have in field Notes, some text typed in SearchView. I read about adding a SearchView, and the best way to implement to search is using a FTS3 or FTS4 Table, allowing the user, for example, type "SUPERMARKET" and find a Transaction where the Notes have this text. The problem appears because a FTS table is slow to perform WHERE conditions (like the above, to filter date)...

How can I implement both Filtering date, using WHERE date BETWEEN ... and ..., and filtering text Notes with the performance of a FTS Table?

Select * FROM Transacions WHERE (Date BETWEEN '2016-04-25' AND '2016-05-14) AND Notes LIKE '%text%''?

Upvotes: 1

Views: 213

Answers (1)

CL.
CL.

Reputation: 180260

Do not think of an FTS table as a table, but as an index.

With the notes indexed like this:

CREATE VIRTUAL TABLE Transactions_FTS USING FTS4(Notes);

you would have to ensure that the IDs of both tables match, and could then combine the tables like this:

SELECT *
FROM Transactions
WHERE Date BETWEEN '2016-04-25' AND '2016-05-14'
  AND _Id IN (SELECT docid
              FROM Transactions_FTS
              WHERE Notes MATCH 'supermarket');

or this:

SELECT *
FROM Transactions
JOIN Transactions_FTS ON Transactions._Id = Transactions_FTS.docid
WHERE Date BETWEEN '2016-04-25' AND '2016-05-14'
  AND Transactions_FTS.Notes MATCH 'supermarket';

(If you care about the amount of storage used, consider an external content FTS table.)

Upvotes: 1

Related Questions