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