SQLite: query with comparison operators in a FTS4 enabled table

Question

SQLite version: 3.12.2

Using this expression I created a table to employ the possibility of fast text queries:

CREATE VIRTUAL TABLE sentence_tbl USING FTS4 (WordsNo INTEGER, Sentence TEXT);

Note 1: WordsNo is the number of words in the Sentence column;

I then populated my table with my data (~ 500,000 rows) and as far as I know, because I used FTS4, I can only use match function to check for the contents of the columns (correct me please if I'm wrong).

What I want to do is to query data with two conditions:

1. All the rows in which the value of WordsNo column is equal to or greater than for example 10;
2. All the rows in which the value of Sentence column has for example 'book' in it.

What I tried to use is:

select * from sentence_tbl where WordsNo >= 10 and Sentence match "book"

But as I said before, comparison operator is not functioning.

What is the proper way to achieve this?

Answer 1

FTS tables always contain strings, regardless of what you try to declare as column type.

To store other data, use both a 'real' table and an FTS table:

CREATE TABLE Sentence (
    ID INTEGER PRIMARY KEY,
    WordsNo INTEGER
);
CREATE VIRTUAL TABLE Sentence_FTS USING FTS4 (
    Sentence
);

SELECT Sentence.WordsNo,
       Sentence_FTS.Sentence
FROM Sentence
JOIN Sentence_FTS ON Sentence.ID = Sentence_FTS.docid
WHERE Sentence.WordsNo >= 10
  AND Sentence_FTS.Sentence MATCH 'book';