Scala Slick update column value

Question

I'm using Slick with Play but am having some problems when trying to update a column value as it is not being updated, although I don't get any error back.

I have a column that tells me if the given row is selected or not. What I want to do is to get the current selected value (which is stored in a DB column) and then update that same column to have the opposite value. Currently (after many unsuccessful attempts) I have the following code which compiles and runs but nothing happens behind the scenes:

val action = listItems.filter(_.uid === uid).map(_.isSelected).result.map { selected =>
      val isSelected = selected.head
      println(s"selected before -> $isSelected")
      val q = for {li <- listItems if li.uid === uid} yield li.isSelected
      q.update(!isSelected)
    }

db.run(action)

What am I doing wrong (I am new to slick so this may not make any sense at all!)

I wrote an answer with better looking syntax. but in short the problem with your code is that result.map must be a result.flatMap. also val isSelected = selected.head may throw an exception if the record does not exist. so consider using selected.headOption instead as I have written in answer — shayan
I've tried the changes you mentioned on the comment above and it works! I just don't understand why the flatMap makes difference when compared with the map. I also prefer your answer, but I can't seem to make it work — LuisF
map and flatMap are two very different things and not just in your example case. Their definition traces back to category theory. you should read on that but basically in exmple terms if you map a function of Int => List[String] to a List[Int] you'll get a List[List[String]] but if you flatMap the same function to the same list you'll get a List[String]] translating it to your example if you flatMap another DBIOAction[T] to your fist DBIOAction[G] the result is a single DBIOAction[T] which is what you want and is the composition of the two actions. — shayan

shayan shayan · Accepted Answer · 2017-06-14T08:27:54

this needs to be seperate actions: one read followed by an update. Slick allows composition of actions in a neat way:

val targetRows = listItems.filter(_.uid === uid).map(_.isSelected)
val actions = for {
  booleanOption <- targetRows.result.headOption
  updateActionOption = booleanOption.map(b => targetRows.update(!b))
  affected <- updateActionOption.getOrElse(DBIO.successful(0))
} yield affected

db.run(actions)

Update:

Just as a side note, RDBMSs usually facilitate constructs for performing updates in one database roundtrip such as updating a boolean column to it's opposite value without needing to read it first and manually negate it. This would look like this in mysql forexample:

UPDATE `table` SET `my_bool` = NOT my_bool

but to my knowledge the high level slick api doesn't support this construct. hence the need for two seperate database actions in your case. I myself would appreciate it if somebody proved me wrong.

Scala Slick update column value

1 Answers