How to use results of one slick query in another with computation in between

Question

I have a database table InventoryLotUsage which has columns id, inventoryLotId, and date. I want to delete an InventoryLot, but before I can do that I need to update the InventoryLotUsage rows that have a foreign key inventoryLotId, based on date and some other conditions.
My question is how do I query for data using monadic joins, do some computations on it and use the result of those computations to run an update all in one transaction in Slick?

I was attempting to get a sequence of rows like this

for {
      il <- InventoryLot.filter(_.id === id)
      lotUsage <- InventoryLotUsage.filter(_.inventoryLotId === id).result
      groupedUsage = lotUsage.groupBy(_.date)
      ...
}

my IDE suggests that lotUsage will be a Seq[InventoryLotUsageRows], but when compiling I get a type error because of the .result.

type mismatch;
 found   : slick.dbio.DBIOAction[FacilityProductRepository.this.InventoryLot,slick.dbio.NoStream,slick.dbio.Effect.Read with slick.dbio.Effect.Read with slick.dbio.Effect.Read]
 required: slick.lifted.Query[?,?,?]
      lotUsage <- InventoryLotUsage.filter(_.inventoryLotId === id).result

Without using .result its type is the InventoryLotUsage table. How can I wrangle the query into something usable for computation?

Valerii Rusakov Valerii Rusakov · Accepted Answer · 2018-07-27T09:52:35

You need to compose DBIOActions to archive desired result. For example:

Load all data that you need

val loadStaffAction = (for {
   il <- InventoryLot.filter(_.id === id)
   lotUsage <- InventoryLotUsage.filter(_.inventoryLotId === id)
} yield (il, lotUsage)).result

Then you could use map/flatMap on loadStaffAction to create update statements based on computations. You can also use for-comprehensions here.

val updateAction = loadStaffAction.map { result =>
  // creating update statements based on some computations and conditions
  DBIO.seq(
    InventoryLotUsage.filter(_.id === inventory1.id).map(_.name).update("new value"),
    InventoryLotUsage.filter(_.id === inventory2.id).map(_.name).update("another value"),
  );
}

After this you can run all queries in one transaction

db.run(updateAction.transactionally)

How to use results of one slick query in another with computation in between

1 Answers