Using SQLite FTS3, how to join or nest SELECT statements to get row plus snippet?

Question

I have an SQLite FTS3 virtual table called categories with columns category, id, header, content. I want to search it, and for the results to contain both the column data and a snippet() result.

The final desired behaviour is to display the category & ID for each match of a search term, and then if the search term is found in the content column, also display each snippet. Like this:

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"

I can get search row results and snippets as separate functions right now. This searches the table and prints out the row.elements correctly:

 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);
            });

        }

This searches for snippets in the content column, and prints out a list of them:

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);
            });

        }

I can so far imagine two possible approaches to this: either I can combine the SELECT queries somehow- although I can't find any examples of JOINing queries using the snippet() function. Or, I can create a new function findSnippet(), and call it after each iteration through the result.rows array. Is either of these approaches likely to work, or is there a better way to handle this?

Answer 1

Just list all the result columns that you want to get for each matching record:

SELECT category,
       id,
       header,
       snippet(guidelines) AS snip
FROM guidelines
WHERE content MATCH 'snail*'