如何查询外部内容FTS4表,但是从原来的内容表中返回附加列 [英] How to query an external content FTS4 table but return additional columns from the original content table
问题描述
我创建一个 FTS4外部内容表中的SQLite这样:
CREATE TABLE t2(id INTEGER PRIMARY KEY, col_a, col_b, col_text);
CREATE VIRTUAL TABLE fts_table USING fts4(content="t2", col_text);
我使用的是外部内容表,以便我不需要 col_text
的重复值存储在 fts_table
。我只索引 col_text
,因为为col_a
和 col_b
唐'T需要索引。
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.
然而,当我这样做 fts_table
的这样的查询
However, when I do a query of fts_table
like this
SELECT * FROM fts_table WHERE fts_table MATCH 'something';
我没有从内容表获得为col_a
和 col_b
T2
。如何返回所有这些列(为col_a
, col_b
, col_text
)从单个FTS查询?
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?
更新
我尝试使用 notindexed =列名
选项,在
CREATE VIRTUAL TABLE fts_table USING fts4(content="t2", col_a, col_b, col_text, notindexed=col_a, notindexed=col_b);
这应该工作的一些人,但我使用它在Android和不支持 notindexed
选项,直到SQLite的3.8系统,Android不支持,直到< A HREF =http://stackoverflow.com/a/4377116/3681880> Android版本5.x的。我需要支持Android 4.x的我更新这个问题,包括Android的标签。
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.
推荐答案
FTS表中有一个名为内部INTEGER PRIMARY KEY列文档ID
或的rowid
。
当插入在FTS表中的行,该列设置为列的原始表中的主键。
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')
这篇关于如何查询外部内容FTS4表,但是从原来的内容表中返回附加列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!