Using FTS as a subquery of an Association & FTS Query Question

[eytanschulman]

Question

First of all thank you @groue for all of your work on GRDB, it's a fantastic interface into SQLite for Apple platforms.

I've built an app that hopefully will be finishing off soon and putting on the store that uses GRDB to power its core, and am adding search functionality with FTS5. I decided on using FTS as opposed to a simple LIKE query as I want the user to be able to search multiple fields simultaneously. I've encountered a syntax-and-maybe comprehending issue that after a few days of diving deep into docs and existing issues, decided to ask here.

Currently working - Before Changes

I have two tables, FormulaSectionPersistable, and FormulaPersistable, with a foreignKey and associations between them.

The front screen of the app shows sections and their formulas within them, in order to do that I created a combined struct to query the association, called FormulaSectionInfo.

Below I attach the definitions of all of these structs for context.

When querying the association without search, everything works fine:

let request = FormulaSectionPersistable.including(all: FormulaSectionPersistable.formulas)
                    .asRequest(of: FormulaSectionInfo.self)
                    .order(FormulaSectionPersistable.Columns.sectionName)

return try request.fetchAll(db)

Attempt to Add FTS

When I attempt to query using FTS and add .matching(pattern) to the association FormulaSectionPersistable.formulas, I get no results.

Now I believe I understand why, due to the fact that FormulaPersistable is not a virtual table with FTS enabled, it is where the content itself is stored, and therefore I had to create an "External Content Full Text Table", which I am doing like this:

try db.create(virtualTable: "\(FormulaPersistable.databaseTableName)_ft", using: FTS5()) { t in
        // Porter tokenizer provides English stemming
       t.tokenizer = .porter()
                
       // Index the content of the sessions table
       // See https://github.com/groue/GRDB.swift#external-content-full-text-tables
       t.synchronize(withTable: FormulaPersistable.databaseTableName)
                
       // The indexed columns
       t.column("name")
       t.column("description")
      t.column("formula")
   }

I managed to get FTS to partially work by creating the following query:

let pattern = FTS5Pattern(matchingAllTokensIn: "\(formulaNameQuery)*")
let formulaPersistableSQL = """
                     SELECT \(FormulaPersistable.databaseTableName).*
                     FROM \(FormulaPersistable.databaseTableName)
                     JOIN \(FormulaPersistable.databaseTableName)_ft
                         ON \(FormulaPersistable.databaseTableName)_ft.rowid = \(FormulaPersistable.databaseTableName).rowid
                         AND \(FormulaPersistable.databaseTableName)_ft MATCH ?
                    """
return try FormulaPersistable.fetchAll(db, sql: formulaPersistableSQL, arguments: [pattern])

Two Questions

FTS Query

This query does return results, although not exactly what I expected, I am trying to understand between the different queries + syntax how to produce my expected result -- The user inputs "met" and any results with the word "metal" will appear. At the moment only once the word is completed do results appear.

I came across this answer #908 (comment) from 2021, but am having difficulty understanding how after splitting the user's input by a space into an array of strings I would then recombine it into a query using Database.makeFTS5Pattern(rawPattern:forTable:)

Combining Association with FTS matching query.

My end goal is to fix the FTS query and inject it as a part of my query for FormulaSectionPersistable, as a part of my association of formulas.
This would mean taking this query:

let request = FormulaSectionPersistable.including(all: FormulaSectionPersistable.formulas)
                    .asRequest(of: FormulaSectionInfo.self)
                    .order(FormulaSectionPersistable.Columns.sectionName)

and filtering the FormulaSectionPersistable.formulas part of it with the FTS query which at the moment I'm doing with Raw SQL.
Is this currently possible with the existing syntax of Associations and Queries?

Would greatly appreciate help on these issues, thank you!

Added Context - Struct definitions

Here's FormulaSectionInfo:

public struct FormulaSectionInfo: Decodable, FetchableRecord, Identifiable {
    public var section: FormulaSectionPersistable
    public var formulas: [FormulaPersistable]
    public var id: Int64? { section.id }
    
    public init(section: FormulaSectionPersistable, formulas: [FormulaPersistable], name: String) {
        self.section = section
        self.formulas = formulas
    }
}

Here's FormulaSectionPersistable

public struct FormulaSectionPersistable: Codable, Hashable, Identifiable {
    public var sectionName: String
    public var hash: String
    public var uuid: String
    public var id: Int64?
    
    enum Columns {
        static let sectionName = Column(CodingKeys.sectionName)
        static let hash = Column(CodingKeys.hash)
        static let uuid = Column(CodingKeys.uuid)
        static let id = Column(CodingKeys.id)
    }
}

// Add Database access

extension FormulaSectionPersistable: FetchableRecord, MutablePersistableRecord {
    public static var databaseTableName: String = "formulaSection"
    // Update auto-incremented id upon successful insertion
    public mutating func didInsert(_ inserted: InsertionSuccess) {
        id = inserted.rowID
    }
}

extension FormulaSectionPersistable {
    static let formulas = hasMany(FormulaPersistable.self, using: ForeignKey([FormulaPersistable.Columns.formulaSectionId]))
    /// The request for the formulaSection's formulas
    var formulas: QueryInterfaceRequest<FormulaPersistable> {
        request(for: FormulaSectionPersistable.formulas)
    }
}

Here is FormulaPersistable:

public struct FormulaPersistable: Codable, Hashable, Identifiable {
    public var name: String
    public var formula: String
    public var unit: String?
    public var description: String
    public var variables: [Variable]
    var formulaSectionId: Int64
    public var hash: String
    public var uuid: String
    public var id: Int64?
    
    enum Columns {
        static let name = Column(CodingKeys.name)
        static let formula = Column(CodingKeys.formula)
        static let unit = Column(CodingKeys.unit)
        static let description = Column(CodingKeys.description)
        static let variables = Column(CodingKeys.variables)
        static let formulaSectionId = Column(CodingKeys.formulaSectionId)
        static let hash = Column(CodingKeys.hash)
        static let uuid = Column(CodingKeys.uuid)
        static let id = Column(CodingKeys.id)
    }
}
extension FormulaPersistable: FetchableRecord, MutablePersistableRecord {
    public static var databaseTableName: String = "formula"
    // Update auto-incremented id upon successful insertion
    mutating public func didInsert(_ inserted: InsertionSuccess) {
        id = inserted.rowID
    }
}

extension FormulaPersistable {
    static let section = belongsTo(FormulaSectionPersistable.self)
    static let results = hasMany(FormulaResult.self, using: ForeignKey([FormulaResult.Columns.formulaId]))
    var results: QueryInterfaceRequest<FormulaResult> {
        request(for: FormulaPersistable.results)
    }
}

Environment

GRDB flavor(s): GRDB
GRDB version: 6.18.0
Installation method: SPM
Xcode version: 14.3.1
Swift version: Swift 5
Platform(s) running GRDB: iOS
macOS version running Xcode: 13.4.1

[groue]

Hello @eytanschulman,

FTS Query

Maybe you need FTS5Pattern(matchingAllPrefixesIn:), that turns any user input into a series of prefix patterns:

if let pattern = FTS5Pattern(matchingAllPrefixesIn: "foo bar") {
    // Prints "foo* bar*"
    print(pattern.rawPattern)
}

This pattern returns document that matches all the prefixes in the user input:

• When the user inputs "met", this builds the pattern "met*" that matches documents that contains met- prefixed words such as "metal" or "metronome".
• If the user want to refine the search, he can type "met gold". The "met* gold*" pattern matches documents that contain both met- and gold- prefixed words, such as "This ring is made of golden metal", as well as "A metronome is gold for the practicing musician."

You can also build other patterns from user input. For example you could only consider the last word as a prefix, if this better fits the needs of your application. When the user types "metal gold", this would build the pattern "metal gold*" that would match "This ring is made of golden metal", but not "golden metallic alloys".

There is no ready-made convenience initializer for this kind of pattern, so you need to build it by hand:

func makeFTS5Pattern(_ db: Database, searchString: String) -> FTS5Pattern? {
    // Prefer `ascii` for processing user input, because it does
    // not alter input words. <https://www.sqlite.org/fts5.html#ascii_tokenizer>
    guard let tokenizer = try? db.makeTokenizer(.ascii()),
          let tokens = try? tokenizer.tokenize(query: searchString).map(\.token)
    else {
        return nil
    }
    
    guard let lastToken = tokens.last else {
        return nil
    }
    
    let rawPattern = (Array(tokens.dropLast()) + ["\(lastToken)*"]).joined(separator: " ")
    return try? db.makeFTS5Pattern(rawPattern: rawPattern, forTable: "formula_ft")
}

try DatabaseQueue().write { db in
    try db.create(virtualTable: "formula_ft", using: FTS5()) { t in
        t.tokenizer = .porter()
        t.column("name")
        t.column("description")
        t.column("formula")
    }
    print(makeFTS5Pattern(db, searchString: "met")!.rawPattern)        // "met*"
    print(makeFTS5Pattern(db, searchString: "metal gold")!.rawPattern) // "metal gold*"
    print(makeFTS5Pattern(db, searchString: "* blurb \"")!.rawPattern) // "blurb*"
}

[groue]

Combining Association with FTS matching query.

Add the following lines to your code.

They provides a convenience method for performing a full-text search directly on formulas. Its implementation defines an association that makes it possible to refer to the formula_ft table from formulas.

extension DerivableRequest<FormulaPersistable> {
    /// Filters formulas that match an FTS5 full-text pattern.
    ///
    /// If pattern is nil, the returned request fetches no row.
    func matching(_ pattern: FTS5Pattern?) -> Self {
        // The association to the full-text table (formula_ft.rowID refers to formula.id)
        let association = FormulaPersistable
            .hasOne(Table("formula_ft"), using: ForeignKey([.rowID]))
            .matching(pattern)
        return joining(required: association)
    }
}

Demo (simplified):

try DatabaseQueue().write { db in
    try db.create(table: "formulaSection") { t in
        t.autoIncrementedPrimaryKey("id")
    }
    
    try db.create(table: "formula") { t in
        t.autoIncrementedPrimaryKey("id")
        t.column("name").notNull()
        t.belongsTo("formulaSection")
    }
    
    try db.create(virtualTable: "formula_ft", using: FTS5()) { t in
        t.tokenizer = .porter()
        t.synchronize(withTable: "formula")
        t.column("name")
    }
    
    let section = try FormulaSectionPersistable().inserted(db)
    _ = try FormulaPersistable(name: "golden metal", formulaSectionId: section.id!).inserted(db)
    _ = try FormulaPersistable(name: "silver metronome", formulaSectionId: section.id!).inserted(db)
    _ = try FormulaPersistable(name: "apple pie", formulaSectionId: section.id!).inserted(db)
    
    // SQL> SELECT * FROM formulaSection
    // SQL> SELECT formula.*
    //      FROM formula
    //      JOIN formula_ft
    //        ON (formula_ft.rowid = formula.id)
    //        AND (formula_ft MATCH 'met*')
    //      WHERE formula.formulaSectionId = 1
    let pattern = FTS5Pattern(matchingAllPrefixesIn: "met")
    let infos = try FormulaSectionPersistable
        .including(all: FormulaSectionPersistable.formulas.matching(pattern))
        //                                                ~~~~~~~~~~~~~~~~~~
        .asRequest(of: FormulaSectionInfo.self)
        .fetchAll(db)
    
    // Prints ["golden metal", "silver metronome"]
    for info in infos {
        print(info.formulas.map(\.name))
    }
}

[eytanschulman]

Thank you very much for the explanation of both the FTS Query and convenience method for matching the query!!

I implemented it and it works great, one last related question..

When performing the combined query, sometimes there are no FormulaPersistable results, but FormulaSectionInfo instances get created (With the FormulaSectionPersistable, but no formulas), which results in an "empty" section.

I am currently performing a simple Swift .filter() on the results like this:

var results = try request.fetchAll(db)
                
results = results.filter { section in
    return section.formulas.count > 0
}

Is there a built-in GRDB-ey methodology of doing this?
I tried adding:

let pattern = FTS5Pattern(matchingAllPrefixesIn: formulaSearchQuery)
                    request = FormulaSectionPersistable.including(all: FormulaSectionPersistable.formulas.matching(pattern))
                        .filter(FormulaSectionPersistable.formulas.count > 0)
//                      ~~~~~~~~~~~~~~~~~~ Added - Gives the following error: 
// Instance method 'filter' requires that 'AssociationAggregate<HasManyAssociation<FormulaSectionPersistable, // FormulaPersistable>.OriginRowDecoder>' (aka 'AssociationAggregate<FormulaSectionPersistable>') conform to // 'SQLSpecificExpressible'
//
                       .asRequest(of: FormulaSectionInfo.self)
                        .order(FormulaSectionPersistable.Columns.sectionName)

[groue]

Your workaround based on .filter { $0.formulas.count > 0 } is totally valid.

FormulaSectionPersistable.formulas.count is an aggregate. Those can't feed the filter method. Those better fit having.

But we're facing a challenge, here 😅 We just found a not-implemented area of GRDB:

// Fatal error: Not implemented: chaining a required association behind an optional association
let pattern = FTS5Pattern(matchingAllPrefixesIn: "met")
let formulas = FormulaSectionPersistable.formulas.matching(pattern)
let infos = try FormulaSectionPersistable
    .including(all: formulas)
    .having(formulas.isEmpty == false)
    .asRequest(of: FormulaSectionInfo.self)
    .fetchAll(db)

We can hack it, though, with another SQL technique:

let pattern = FTS5Pattern(matchingAllPrefixesIn: "met")
let formulas = FormulaSectionPersistable.formulas.matching(pattern)
let infos = try FormulaSectionPersistable
    .including(all: formulas)
    .joining(required: formulas)
    .distinct()
    .asRequest(of: FormulaSectionInfo.self)
    .fetchAll(db)

Maybe check the performance of the various techniques, if your database is big.

[eytanschulman]

Gotcha, thank you!

I greatly appreciate the help.
Cheers!

[groue]

You're welcome, @eytanschulman! And thanks for sponsoring the project ❤️ Happy GRDB!