Updating multiple rows using instr

Question

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?

Answer 1

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.

Answer 2

Here is how I solved it with raw SQL in case it is too complicated to extend the framework. I choose so, because I think this is easier to understand for someone who needs to read the code and has no experience with GRDB or frameworks in general.

do {
  var dbQueue:DatabaseQueue? = try DatabaseQueue(path: "PATH_TO_DATABASE")
  try dbQueue?.write { db in
    try db.execute(
      sql: "UPDATE MyTable SET path = :path WHERE instr(title, :title)",
      arguments:  ["path": path, "title": title]
    )
  }
} catch {
  print("UPDATE MyTable \(error)")
}