How to mix select and delete in a Slick transaction

Question

Why does it not work to combine a SELECT and a DELETE statement in a Slick query? as in:

 val query = (for {
     item <- SomeTable
     _ <- OtherTable.filter(_.id === item.id).delete
 } yield ()).transactionally

"Cannot resolve symbol 'transactionally'"

(without .transactionally, it is a Query[Nothing, Nothing, Seq], if that helps) while the two actions work separately:

 val query = (for {
     item <- SomeTable
 } yield ()).transactionally

,

 val query = (for {
     _ <- OtherTable.filter(_.id === 2).delete
 } yield ()).transactionally

Answer 1

OK so this is a classic example of mixing DBIO with Query.

In your first case:

val query = (for {
     item <- SomeTable // this is `Query`
     _ <- OtherTable.filter(_.id === item.id).delete // this is `DBIO`
 } yield ()).transactionally

Obviously for DML you can use only actions (Query is for DQL - being simply SELECT).

So first thing is - change your code to use only DBIOs. Below example is incorrect.

val query = (for {
     item <- SomeTable.result // this is `DBIO` now
     _ <- OtherTable.filter(_.id === item.id).delete // but this won't work !!
 } yield ()).transactionally

OK, we are nearly there - the problem is that it doesn't compile. What you need to do is to be aware that now this part:

item <- SomeTable.result

returns Seq of your SomeTable case class (which among other things contains your id).

So let's take into account:

val query = (for {
     items <- SomeTable.result // I changed the name to `items` to reflect it's plural nature
     _ <- OtherTable.filter(_.id.inset(items.map(_.id))).delete // I needed to change it to generate `IN` query
 } yield ()).transactionally