6

Is it possible to update variable column list, which number is know only in runtime by slick 3.0?

Below is example what I want to do (won't 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)
user2860204
  • 151
  • 9

3 Answers3

2

Here is what I've done in Slick 3.1. I wasn't sure what worse, editing plain SQL statement or multiple queries. So I decided to go with latter assuming Postgres optimizer would see same WHERE clause in update queries of single transaction. My update method looks like this

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): _*))
}
expert
  • 29,290
  • 30
  • 110
  • 214
  • 4
    I think the last line will make that each action uses its own transaction. To have the same transaction for the whole sequence of actions, we should use: `slickDb.run(DBIO.seq(actions: _*).transactionally)` – Pascal Dimassimo Apr 14 '16 at 18:13
  • How can I check the result of the transaction? From just running one update you get the numbers of rows modified. – spydon Aug 07 '17 at 21:40
0

In Slick 3.0 they adopted slightly different approach, instead of having updateAll methods, as far as I userstand path of combinators was adopted.

So main idea is to define some actions on the data and then combine them ont he database to make a single run. Example: 

// let's assume that you have some table classes defined somewhere

// then let's 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

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

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

Artemis
  • 4,821
  • 3
  • 21
  • 24
0

to update a variable number of columns you may use this 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)

}

evgnomon
  • 772
  • 8
  • 9