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.

Answer 1

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
    }))
  }