我有一个名为categories
的SQLite FTS3虚拟表,其中包含列category, id, header, content
。我想搜索它,并让结果同时包含列数据和snippet()
结果。
最后想要的行为是显示类别&搜索词的每个匹配项的ID,然后如果在内容列中找到该搜索词,还显示每个片段。像这样:
Search term "virus":
Category: Coughing
ID: Coughing Symptoms
Snippet 1: "the common cold, which is spread by the cold <b>virus</b> blah blah etc"
Snippet 2: "lorem ipsum some other things <b>virus</b> and then some other stuff"
Category: Coughing
ID: Coughing treatment
Snippet 1: "...coughing can be treated by managing the symptoms. If a <b>virus</b> blah etc"
Category: Headache
ID: Headache Symptoms
Snippet: "I think you get the idea now <b>virus</b> more things"
我现在可以将搜索行结果和代码段作为单独的函数获取。这将搜索表并正确打印出行元素:
function SearchValueInDB() {
db.transaction(function(transaction) {
var search = $('#txSearch').val(),
query = "SELECT * FROM guidelines WHERE guidelines MATCH '" + search + "*';";
transaction.executeSql(query,[], function(transaction, result) {
if (result != null && result.rows != null) {
if (result.rows.length == 0) {
//no results message
} else {
for (var i = 0; i < result.rows.length; i++) {
var row = result.rows.item(i);
$('#lbResult').append('<br/> Search result:' + row.category + ' ' + row.id + ' ' + row.header + '<br/>');
}
}
}
},nullHandler,errorHandler);
});
}
这将在content
列中搜索片段,并打印出它们的列表:
function SearchForSnippet() {
db.transaction(function(transaction) {
var search = $('#txSearch').val(),
query = "SELECT snippet(guidelines) FROM guidelines WHERE content MATCH '" + search + "*';";
transaction.executeSql(query,[], function(transaction, result) {
if (result != null && result.rows != null) {
$('#lbResult').html('');
for (var i = 0; i < result.rows.length; i++) {
var row = result.rows.item(i);
for(var key in row) {
var value = row[key];
$('#lbResult').append('<br/> ' + i + value );
}
}
}
},nullHandler,errorHandler);
});
}
到目前为止,我可以想象两种可能的方法:要么我可以以某种方式组合SELECT查询——尽管我找不到任何使用snippet()函数的JOINing查询的例子。或者,我可以创建一个新函数findSnippet()
,并在每次迭代后通过result.rows
数组调用它。这两种方法中的任何一种可能奏效吗,或者有更好的方法来处理这一问题吗?
只需列出每个匹配记录想要获得的所有结果列:
SELECT category,
id,
header,
snippet(guidelines) AS snip
FROM guidelines
WHERE content MATCH 'snail*'