Slick 3.0 how to update the list of variable columns whose number is known only at runtime

Question

Is it possible to update the list of variable columns, what number is known only at runtime of slick 3.0?

Below is an example of what I want to do (will not compile)

var q: Query[UserTable, UserTable#TableElementType, Seq] = userTable var columns = List[Any]() var values = List[Any]() if (updateCommands.name.isDefined) { columns = q.name :: columns values = updateCommands.name.get :: values } if (updateCommands.surname.isDefined) { columns = q.surname :: columns values = updateCommands.surname.get :: values } q = q.filter(_.id === updateCommands.id).map(columns).update(values) 

Answer 1

Here is what I did in Slick 3.1. I was not sure which was worse when editing a simple SQL statement or multiple queries. So I decided to go with the latter, assuming that the Postgres optimizer will see the same WHERE in update requests for one transaction. My update method is as follows:

 def updateUser(user: User, obj: UserUpdate): Future[Unit] = { val actions = mutable.ArrayBuffer[DBIOAction[Int, NoStream, Write with Transactional]]() val query = users.withFilter(_.id === user.id) obj.name.foreach(v => actions += query.map(_.name).update(v)) obj.email.foreach(v => actions += query.map(_.email).update(Option(v))) obj.password.foreach(v => actions += query.map(_.pwdHash).update(Option(encryptPassword(v)))) slickDb.run(DBIO.seq(actions.map(_.transactionally): _*)) } 

Answer 2

In Slick 3.0, they took a slightly different approach, instead of having updateAll methods, as I understand it, using combinators.

So, the main idea is to define some actions on the data, and then combine them into a database to make one run. Example:

 // let assume that you have some table classes defined somewhere // then let define some actions, they might be really different val action: SqlAction = YourTable.filter(_id === idToAssert) val anotherAction = AnotherTable.filter(_.pets === "fun") // and then we can combine them on a db.run val combinedAction = for { someResult <- action anotherResult <- anotherAction } yeild (someResult,anotherResult) db.run(combinedAction) // that returns actual Future of the result type 

Likewise, you can deal with lists and sequences, take a look here: http://slick.typesafe.com/doc/3.1.0-M1/dbio.html DBIO has some functions that allow you to combine a list of actions with one action.

I hope the idea is clear, if you have questions, you are nice to the comments.

Answer 3

to update a variable number of columns that you can use in the same way as I used for slick 3:

  def update(id: Long, schedule: Schedule, fieldNames: Seq[String]): Future[_] = { val columns = schedules.baseTableRow.create_*.map(_.name).toSeq.filter(fieldNames.map(_.toUpperCase).contains) val toBeStored = schedule.withDefaults val actions = mutable.ArrayBuffer[DBIOAction[Int, NoStream, Write with Transactional]]() val query = schedules.withFilter(_.id === id) //this is becasue of limitations in slick, multiple columns are not possible to be updated! columns.find("NAME".equalsIgnoreCase).foreach(x => actions += query.map(_.name).update(toBeStored.name)) columns.find("NAMESPACE".equalsIgnoreCase).foreach(x => actions += query.map(_.namespace).update(toBeStored.namespace)) columns.find("URL".equalsIgnoreCase).foreach(x => actions += db.run(DBIO.seq(actions: _ *).transactionally.withPinnedSession) 

}