4

I am creating an FTS4 external content table in SQLite like this:

CREATE TABLE t2(id INTEGER PRIMARY KEY, col_a, col_b, col_text);
CREATE VIRTUAL TABLE fts_table USING fts4(content="t2", col_text);

I'm using an external content table so that I don't need to store duplicate values of col_text in fts_table. I'm only indexing col_text because col_a and col_b don't need to be indexed.

However, when I do a query of fts_table like this

SELECT * FROM fts_table WHERE fts_table MATCH 'something';

I don't have access to col_a and col_b from the content table t2. How do return all these columns (col_a, col_b, col_text) from a single FTS query?

Update

I tried using the notindexed=column_name option as in

CREATE VIRTUAL TABLE fts_table USING fts4(content="t2", col_a, col_b, col_text, notindexed=col_a, notindexed=col_b);

This should work for some people, but I am using it in Android and the notindexed option isn't supported until SQLite 3.8, which Android doesn't support until Android version 5.x. And I need to support android 4.x. I am updating this question to include the Android tag.

Community
  • 1
  • 1
Suragch
  • 484,302
  • 314
  • 1,365
  • 1,393

1 Answers1

10

FTS tables have an internal INTEGER PRIMARY KEY column called docid or rowid. When inserting a row in the FTS table, set that column to the primary key of the row in the original table.

Then you can easily look up the corresponding row, either with a separate query, or with a join like this:

SELECT *
FROM t2
WHERE id IN (SELECT docid
             FROM fts_table
             WHERE col_text MATCH 'something')
CL.
  • 173,858
  • 17
  • 217
  • 259
  • That would require a second query, right? And I would have to do that for every row that the FTS query returned, wouldn't I? Is there any way to do this with a single query (in order to return an Android cursor that contains multiple rows)? – Suragch Apr 27 '15 at 08:51
  • but, with a query from other query It is not a more slow than only one fts query?? – David Untama Aug 25 '16 at 03:17
  • `id` is the primary key, so this lookup will be very fast. (This is pretty much the same mechanism that the DB would use for a search using a 'normal' index.) – CL. Aug 25 '16 at 06:39
  • @CL. What do you think about a contentless FTS table as an alternate to this method? (See the new answer below.) Does that already do the same thing that I was trying to accomplish in my question? – Suragch Feb 23 '17 at 07:26