Deleting Rows from multiple tables in a slick query

Question

I googled and found this thread Slick 3.0: Delete rows from multiple tables in a transaction However the solution says that its not a good solution.

I am deleting rows from multiple tables with my code below

val deleteB = for {
  aId <- TableA.filter(a => a.id === param).map(_.id)
  bId <- TableB.filter(_.aId === aId)
} yield bId

val deleteC = for {
  aId <- TableA.filter(a => a.id === param).map(_.id)
  cId <- TableC.filter(_.aId === aId)
} yield cId

val deleteA = TableA.filter(a.Id === param)

val query = (deleteC.delete andThen deleteB.delete andThen deleteA.delete).transactionally
db.run(query)

But I wonder if there is a better way of writing this.

My problem with the approach above is that I want to return the number of rows deleted from TableA and not the sum of rows deleted from child tables TableB and TableC.

Also, at runtime it complains about having joins in the delete query.

I dont see any issues with approach. It should work just fine.. are you facing any specific issues in deleting the records? — rogue-one, Feb 02 '17 at 00:22

rogue-one · Answer 1 · 2017-02-02T01:29:33.340

The following would resolve the join warning/error.

val deleteB = TableB.filter(_.aid in TableA.filter(a => a.id === id).map(_.id))

val deleteC = TableC.filter(_.aid in TableA.filter(a => a.id === id).map(_.id))

val deleteA = TableA.filter(_.id === id)

db.run((deleteB.delete andThen deleteC.delete andThen deleteA.delete).transactionally)

And since you use andThen to chain your actions, the composed action would always return the row affected count of the last action as stated here. So the returned number is always the rows deleted from the deleteA action since that is the last action in the andThen chain.

andThen

Run another action after this action, if it completed successfully, and return the result of the second action. If either of the two actions fails, the resulting action also fails.

Thank you! That's a much cleaner solution. – Alexander Eliseyev Aug 16 '18 at 05:35 — Alexander Eliseyev, Aug 16 '18 at 05:35

score 2 · Accepted Answer · answered Feb 02 '17 at 04:15

I think you can do this way also -

 def buildTransactionQuery = {
    for {
      deleteA <- TableA.filter(a.Id === param)
      deleteB  <- TableB.filter(_.aId === deleteA.map(_.id))
      deleteC  <- TableC.filter(_.aId === deleteA.map(_.id))

      deleteAAction = deleteA.delete
      deleteBAction = deleteB.delete
      deleteCAction = deleteC.delete
      res = (deleteAAction, deleteBAction, deleteCAction)
    } yield res
  }

  def executeTransactionQuery = {
    val transactionQuery = for {
      queries <- buildTransactionQuery
      action = DBIOAction.seq(queries._3, queries._2, queries._1)
    } yield action
    transactionQuery.flatMap(action => db.run(action.transactionally).transform(s => true, t => {
      logger.error(t.getMessage)
      t
    }))
  }

Does this work? I think you are executing each action in their own transactions. But we need to run all 3 in a single transaction. — Knows Not Much, Feb 02 '17 at 13:26
It will work, executing 3 actions in a single transaction here. — Jet, Feb 03 '17 at 05:37

Deleting Rows from multiple tables in a slick query

2 Answers2