Tuesday, March 1, 2016

Scala slick - Multiple update with different values

Hi. Again, something that I was expecting to easily find online, but had to implement by myself.

Slick, by typesafe, is a modern database query and access library for Scala. 

We are using slick 3.1.1 in order to access our db.

At some day, we had to update multiple rows, with different values, depending on one of the columns. The new values are coming out of a sequence of one of our objects.

In addition we wanted to do some more inserts, as part of the same transaction.

A simple sql that looks like that: "update table cars set proce=c.price where year=c.year"

Lets say that we want to update a person's details, and to update all of the person's cars' condition and price, according to the sequence of cars that are part of the person object.

We find the corresponding cars in the db with filtering on the car Id column that links between the cars sequence to the cars  table.

We also want to update the person record with the person object in the same transaction

Solution: We are preparing a dbio object of sequence of update actions, and then combining the DBIO of sequence with the person update action, under the same DBIO.seq call.

With slick it looks like that:

def updatePerson(pers: Person, action: String) = {
  val cars = pers.cars
  val update_cars=cars.map(b=> {
    db_cars.filter(cdb=> cdb.car_id===b.car_id).map(r=>(r.condition,r.price)).update(b.condition,b.price)
  val dbio_up_cars= DBIO.sequence(update_cars)

    db_persons.filter(_.id === pers.id).update(fromPerson(pers)), 

//val db_persons = TableQuery[DbPersons]

where, for that example, the person db object looks like that:

class DbPerson(tag: Tag) extends Table[(Long)](tag, "Persons") {
def id = column[Long]("ID", O.PrimaryKey)

def * = (Id)
val db_person = TableQuery[DbPerson]

Good Luck!

No comments:

Post a Comment