Using array in SQL arguments

[chiliec]

I'd like to use in sql expressions with array:

// SELECT * FROM table WHERE id IN (1,2,3,5,8)
db.execute("SELECT * FROM table WHERE id IN (?)", arguments: [[1,2,3,5,8]] )

but I get an error:
Contextual type 'DatabaseValueConvertible' cannot be used with array literal
What do you think about this?

[groue]

Hello @chiliec

GRDB supports your precise use case through the RowConvertible protocol:

struct Book: RowConvertible, DatabaseTableMapping {
    static func databaseTableName() -> String { return "table" }
    static func fromRow(row: Row) -> Book {
        return Book(...)
    }
}

dbQueue.inDatabase { db in
    // SELECT * FROM table WHERE id IN (?,?,?)
    let books = Book.fetchAll(db, keys: [1,2,3])
}

More generally speaking, I wish GRDB would support arrays in arguments. I even sent a pull request to FMDB which exactly supports this feature.

But I don't know how to make Swift accept as arguments an array of values or arrays of values:

// Swift can't express the type of [1,[2,3,4]]
db.execute("SELECT * FROM table WHERE  x > ? AND y IN (?)", arguments: [1,[2,3,4]])

Precisely speaking, Swift supports the [1,[2,3,4]] literal thanks to Foundation. But it fails to compile values that NSArray would not support (nil, enum cases, etc.):

import Foundation
let a = [1, [2,3,4]]    // Array<NSObject>
let b = [nil, [2,3,4]]  // error: type of expression is ambiguous without more context

[groue]

@chiliec: I'm closing this issue since you did not answer, and since Swift actively prevents us from implementing your feature request.

[siuying]

@groue I noticed collection.contains(column) worked for select statement, but what if I want to do an update like this?

db.execute("UPDATE table SET state = ? where id in (?)", arguments: ["completed", [1,2,3,5,8]] )

[groue]

Hello @siuying

For SQLite a ? placeholder represents a simple value, and can't be used for an array.

A request such as UPDATE table SET state = ? where id in (?) must thus be written as UPDATE table SET state = ? where id in (?,?,...), where the IN operator is followed by as many question marks as there are ids.

Following the same idea, the arguments to a query are only made of values, and won't accept arrays. Each value in a query arguments matches its own ? in the SQL query, from left to right.

You would thus write something like:

let ids = [1,2,3,5,8]
let questionMarks = repeatElement("?", count: ids.count).joined(separator: ",")
let query = "UPDATE t SET state = ? WHERE id IN (\(questionMarks))"
var arguments: StatementArguments = ["completed"]
arguments += StatementArguments(ids)
print(arguments) // Prints ["completed", 1, 2, 3, 5, 8]
try db.execute(query, arguments: arguments)

[siuying]

Thank you for your pointer! I don't know where do I get the idea I can use array here... everything make sense now, thanks again!

[martindufort]

Is this still the recommend mechanism of injecting an array of IDS into an IN clause ?
Anything new in GRDB 5.0 that supersedes this ?

[groue]

When you build a raw SQL string, you have to embed as many question marks as there are arguments. That's how SQLite interprets SQL:

let ids = [1, 2, 3]
let questionMarks = databaseQuestionMarks(count: ids.count) // "?,?,?"
let sql = "SELECT * FROM player WHERE id IN (\(questionMarks))"
let arguments = StatementArguments(ids)
let players = try Player.fetchAll(db, sql: sql, arguments: arguments)

You can also use SQL Interpolation:

let ids = [1, 2, 3]
let request: SQLRequest<Player> = "SELECT * FROM player WHERE id IN \(ids)"
let players = try request.fetchAll(db)