How to access an FTS table in SQLite.swift using the IN condition

Question

I'm trying to query an FTS table using SQLite.swift. Previously I have done it in Android. The essence of what I am trying to do is this:

SELECT *
FROM t2
WHERE id IN (SELECT docid
             FROM fts_table
             WHERE col_text MATCH 'something')

From the SQLite.swift documentation I see the IN condition can be written like this:

users.filter([1, 2, 3, 4, 5].contains(id))
// SELECT * FROM "users" WHERE ("id" IN (1, 2, 3, 4, 5))

And virtual tables can be queried like this:

let wonderfulEmails = emails.match("wonder*")
// SELECT * FROM "emails" WHERE "emails" MATCH 'wonder*'

let replies = emails.filter(subject.match("Re:*"))
// SELECT * FROM "emails" WHERE "subject" MATCH 'Re:*'

However, I can't figure out how to combine these. I don't really want to have to execute arbitrary SQL (although this is now working thanks to help from this answer).

Update

Here is my most recent try that is not working:

let tableText = Table("t2")
let id = Expression<Int64>("id")
let someColumn = Expression<String>("someColumn")

let tableFts = VirtualTable("fts_table")
let columnDocId = Expression<Int64>("docId")


let ftsQuery = tableFts
    .select(columnDocId)
    .filter(tableFts.match("something"))
let tableQuery = tableText
    .filter((try db.prepare(ftsQuery))
        .contains(id))                       // <-- error

for row in try db.prepare(tableQuery) {
    print(row[someColumn])
}

where the line with contains(id) throws the error:

Cannot convert value of type 'Expression' to expected argument type '@noescape (Row) throws -> Bool'

Nyctaginaceous

Do you have an example code snippet and failure? What are you trying to do that doesn't work? – Ilianailine

@stephencelis, I have added an update. In this latest attempt I tried breaking it up into two queries since I don't know of any way to do it in a single query. – Nyctaginaceous

I don't believe that you'll be able to use the Expressions in a query of the FTS tables. If you look at the db schema of your fts table you'll note that the type column is empty rather than showing expected types (as noted in a regular table). Unsure of why sqlite works like this but the only thing I've been able to get working is looping through the statement rows returned. Would love to know if you've been able to advance this further. – Calica

@TommieC., I ended up just using a raw SQLite query. I added it just now for you as an answer below. – Nyctaginaceous

Answer 1

I ended up just running the raw SQL command. This doesn't exactly answer what I was asking in the question (since it doesn't use the pure SQLite.swift API) but for those who just need to get something working, ... it works.

Here is a modified example that I pulled from some of my source code.

static func findLinesContaining(_ searchTerms: String, forSearchScope scope: SearchScope) throws -> [TextLines] {

    guard let db = SQLiteDataStore.sharedInstance.TextDB else {
        throw DataAccessError.datastore_Connection_Error
    }

    // TODO: convert to SQLite.swift syntax rather than using a SQL string
    var statement: Statement? = nil
    switch scope {
    case .wholeCollection:
        statement = try db.run("SELECT bookId, chapterId, lineId, lineText" +
            " FROM lines" +
            " WHERE rowid IN (SELECT docid FROM fts_table" +
            " WHERE fts_table MATCH ?);", searchTerms)

    case .singleBook(let chosenBookId):
        statement = try db.run("SELECT bookId, chapterId, lineId, lineText" +
            " FROM lines" +
            " WHERE rowid IN (SELECT docid FROM fts_table" +
            " WHERE fts_table MATCH ?) AND bookId = ?;", [searchTerms, chosenBookId])
    }


    var returnList: [TextLine] = []
    for row in statement! {

        let line = TextLine()
        if let bookId = row[0] as? Int64,
            let chapterId = row[1] as? Int64,
            let lineId = row[2] as? Int64,
            let text = row[3] as? String {

            line.bookId = Int(bookId)
            line.chapterId = Int(chapterId)
            line.lineId = Int(lineId)
            line.lineText = text
        }

        returnList.append(line)
    }

    return returnList
}

The above code is untested after renaming variables and classes for use on SO. You have my permission to edit the code if you find a bug.

Nyctaginaceous

Thanks! I tried something similar but was not using the parameter placeholders (?); which I will be moving forward. +1 for the useful share. – Calica