Fetching Count on a FTS4 Full-text Search

Related Projects GRDB
1

So I can successfully fetch the search result with this query:

    let pattern = FTS3Pattern(matchingPhrase: searchQuery.lowercased())
    let sql = """
        SELECT paragraph.*
    FROM paragraph
    JOIN paragraph_ft
        ON paragraph_ft.id = paragraph.id
        AND paragraph_ft MATCH ?
    """

        try AppDatabase.shared.databaseReader.read { db in
            let documents = try Paragraph
                .fetchAll(db, sql: sql, arguments: [pattern])

but to protect from searches that result in massive results(like when the user searches for the word "the") I'd like to first make a query that fetches the count of the query alone and then refrain from fetching the full result if it is too large(say, over 1000 results).

How do I fetch a count using the FTSPattern() API? I tried substituting the string directly:

        let paragraphCount = try AppDatabase.shared.databaseReader.read { db in
    try Int.fetchOne(db, sql: "SELECT COUNT(*) FROM paragraph JOIN paragraph_ft ON paragraph_ft.id = paragraph.id AND paragraph_ft MATCH \(searchQuery.lowercased())")!
}

but results in an error:

2022-03-26 15:13:20.782379-0400 TheWord[12265:935274] [general] error: SQLite error 1: no such column: the - while executing `SELECT COUNT(*) FROM paragraph JOIN paragraph_ft ON paragraph_ft.id = paragraph.id AND paragraph_ft MATCH the`

Here are my tables:

        try db.create(table: "paragraph") { t in
            print("created: \(t)")
            t.column("id", .text).primaryKey()
            t.column("paragraph_number", .integer)
            t.column("paragraph_text", .text)
            t.column("paragraph_ordinal", .integer)
            t.column("chapter_number", .integer)
            t.column("book_name", .text)
            t.column("book_ordinal", .integer)
            t.column("inserted_at", .text)
            t.column("updated_at", .text)
        }

        try db.create(virtualTable: "paragraph_ft", using: FTS4()) { t in
            t.tokenizer = .porter
            t.compress = "zip"
            t.uncompress = "unzip"
            t.synchronize(withTable: "paragraph")
            t.column("id")
            t.column("paragraph_text")
        }

Not sure what API to use to do this..
regards,
Michael

2

Hello @mazz,

Check the Avoiding SQL injection documentation chapter of GRDB, and use arguments, just like you did in the first request (sql: "...", arguments: ...).

It's really important that you understand that you must NEVER NEVER NEVER embed values directly in your SQL strings. Perform an internet search for "SQL injection" with Google or your favorite search engine, look at the number of results, and understand how important it is. Click the links and learn more about it.

2 Likes
3

Thanks for the warning @gwendal.roue.

My first mistake was to pass the wrong param name to fetchOne() (used pattern: instead of arguments:) which lead me to a bad question(yet again). Hope this helps someone else.

Future people .. this works:

let count = try AppDatabase.shared.databaseReader.read { db in
    try Int.fetchOne(db, sql: "SELECT COUNT(*) FROM paragraph JOIN paragraph_ft ON paragraph_ft.id = paragraph.id AND paragraph_ft MATCH ?", arguments: [pattern])!
}
1 Like
4

Instead of running two queries for every “normal” search where you don’t abort you could instead run the search with a LIMIT in general, i.e.

    JOIN paragraph_ft
        ON paragraph_ft.id = paragraph.id
        AND paragraph_ft MATCH ?
    LIMIT 100

In this case you’ll probably want to add some ORDER BY criterion as well to ensure those results are the most relevant ones.

5

I also want to show what they would've obtained, like:

(28141 paragraphs - only first 1000 shown)

At the moment I'm not sure how to do that without doing two queries, one for count and one for actual results.

1 Like