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, Jun 14 '17 at 09:30
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, Jun 14 '17 at 22:50
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, Jun 14 '17 at 23:38

shayan · Accepted Answer · 2018-03-16T18:57:58.410

5

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.

edited Mar 16 '18 at 18:57

answered Jun 14 '17 at 08:27

shayan

1,211
9
12

Well, the code compiles and runs without hickups, but when I go and check the value is still not updated... is it possible that it has to do with the DBMS I'm using (H2 for dev environment)? – LuisF Jun 14 '17 at 22:43
I'm not in the position to reproduce the error at the moment but if you are checking the results from the db in the same script after inserting there shoudn't be a problem. I've written basically your same script here. can you remove the ```.take(1)``` bit and run it again? also make sure your check is done after insertion either using a flatMap construct as pointed out or use ```Await.result``` – shayan Jun 14 '17 at 23:45
also both in your example and my answer the returning result of executed query is an integer indicating the number of rows affected. is it zero in mine and 1 in yours? – shayan Jun 14 '17 at 23:51
It was related to the `.take(1)`part. I've removed it and it works now. When making the check you've said, with the `take`part it doesn't print anything, but it prints 1 when removed. I was under the impression the take part removed the first row and then proceeded doing something only on that row, right? By removing it, in case the first map returned more than one row, wouldn't it update all rows? Anyway, can you please update your answer so that I can mark it as correct? Thanks! – LuisF Jun 15 '17 at 13:58
```.take(1)``` is equal to mysql ```Limit 1``` and contradictory to my assumption, it skips the first would be affected row instead of limiting the effect to one row. so my bad there. I updated it. – shayan Jun 15 '17 at 16:34
I read it on stackoverflow that apparently why ```Limit 1 ``` doesn't work here is explained [here](https://stackoverflow.com/questions/23183237/update-and-limit-in-mysql) – shayan Jun 15 '17 at 16:40

Scala Slick update column value

1 Answers1