Updating multiple rows using instr-CodePudding

I am trying to make the following call:

UPDATE MyTable SET path = ? WHERE instr(title, ?) AND start - ? < 60

However I have not been able to use instr with GRDB.

_ = try dbQueue?.write { db in
    try MyTable
        .filter(Column("start") > date - 60)
        .filter(title.contains(Column("title")))
        .updateAll(db,
                   Column("path").set(to: path)
        )
}

How can I do this correctly? Could I also run a raw query instead? How can I fill the ? with my variables if using a raw query?

CodePudding user response：

GRDB does not ship with built-in support for the instr function. You can define it in your code:

func instr(_ lhs: some SQLExpressible, rhs: some SQLExpressible) -> SQLExpression {
    SQL("INSTR(\(lhs), \(rhs))").sqlExpression
}

// SELECT * FROM myTable WHERE instr(?, title)
let title: String = ...
let request = MyTable.filter(instr(title, Column("title")))

// UPDATE myTable SET path = ? WHERE instr(?, title)
let path: String = ...
try request.updateAll(db, Column("path").set(to: path))

See the How do I print a request as SQL? faq in order to control the SQL generated by GRDB.