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

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:

  • Encrypting SQLite database file on iOS
  • Firebase Offline Storage Advanced - Manual Sync and Progress Information
  • Setting user_version in sqlite
  • Is there a sqlite .dump equivalent in objective-c?
  • Sqlite File Location Core Data
  • SQLite insert works in simulator but not on device
  • 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’

    Solutions Collect From Internet About “How to access an FTS table in SQLite.swift using the IN condition”

    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.