1

I am working on full text search on sqlite using fts4.

In my DB I have a normal table Notes and it contains 33 records.

I created a virtual table using fts4 like this

CREATE VIRTUAL TABLE t3 USING fts4(content="Notes", user_notes);

I am querying it like this

SELECT user_notes FROM t3 WHERE user_notes MATCH 'important'

And this

SELECT user_notes FROM t3 WHERE t3 MATCH 'important'

But non of the query is working, Why? I am getting empty result no error.

S.J
  • 3,063
  • 3
  • 33
  • 66
  • You get an empty result because there are not matching rows. – CL. Jan 24 '17 at 12:59
  • @CL. Please tell how I am not matching the row in this query SELECT user_notes FROM t3 WHERE user_notes MATCH 'important' ..... user_note is the row..... – S.J Jan 25 '17 at 06:02
  • What row? Why are you assuming that there is a matching row? – CL. Jan 25 '17 at 06:22
  • @CL. Yes in my row user_note I have 'important' word, then why its not matching – S.J Jan 25 '17 at 06:41
  • Show that row (with a SELECT, or the code that is inserting it). – CL. Jan 25 '17 at 06:43
  • @CL. I create the fts4 table like this CREATE VIRTUAL TABLE t3 USING fts4(content="Notes", user_notes); ..... when I query SELECT * FROM t3, I can see all 33 user notes. If I insert new row in Notes table it is automatically in t3 table also. Now I am querying t3 SELECT user_notes FROM t3 WHERE user_notes MATCH 'important' I don't get any result, But if I replace Match with = I get the result..... Why Match keyword is not returning any result? – S.J Jan 25 '17 at 07:58

2 Answers2

3

The documentation says:

The FTS4 module never writes to the content table, and writing to the content table does not affect the full-text index. It is the responsibility of the user to ensure that the content table and the full-text index are consistent.

So when you insert into the Notes table, you must also insert into the t3 table. (If the actual table is already filled, use the rebuild FTS command.)

CL.
  • 173,858
  • 17
  • 217
  • 259
  • 1
    CREATE VIRTUAL TABLE t3 USING fts4(content="Notes", user_notes); after this query when I select like this SELECT * FROM t3 I can see all the records, I didn't insert any record explicitly, then why its showing all the records? – S.J Jan 25 '17 at 11:17
  • Becaus it isn't using the index (this is the whole point of an external content table). – CL. Jan 25 '17 at 12:17
  • means after this CREATE VIRTUAL TABLE t3 USING fts4(content="Notes", user_notes); I have to insert data in t3, then Match keyword will work? – S.J Jan 26 '17 at 11:35
-6

Have you tried using the 'LIKE' operator instead?

SELECT user_notes FROM t3 WHERE user_notes LIKE'%important%'
Franz Stoneking
  • 37
  • 1
  • 6