Slick 3.0: Delete rows from multiple tables in a transaction

Question

I want to delete rows from a few tables. My exact intention is depicted in the pseudo SQL statements below,

delete from users where oid={user_oid};
login_infos_oid = select login_infos_oid from users_login_infos where users_oid={user_oid};
delete from users_login_infos where users_oid={user_oid};
delete from password_infos where login_infos_oid={login_infos_oid};
delete from login_infos where oid={login_infos_oid};

users_login_infos table has 2 columns users_oid and login_infos_oid and joins users and login_infos tables . How do I this nicely in Slick 3.x within a transaction? Thanks.

Answer 1

I do not think this is a good solution but for the time being this is what it is,

val userQuery = slickUsers.filter(_.username === username)
val userLoginInfoQuery = slickUserLoginInfos.filter(_.userOid in userQuery.map(_.oid))
val loginInfoQuery = slickLoginInfos.filter(_.oid in userLoginInfoQuery.map(_.loginInfoOid))
val passwordInfoQuery = slickPasswordInfos.filter(_.loginInfoOID in userLoginInfoQuery.map(_.loginInfoOid))

db.run((loginInfoQuery.delete andThen
  passwordInfoQuery.delete andThen
  userLoginInfoQuery.delete andThen
  userQuery.delete).transactionally)

Answer 2

The reason Slick doesn't currently support this out of the box (at least up until version 3.3.3 or greater) is because not all database systems support this kind of query, such as SQLite. Also, the syntax is not exactly the same for all database systems as well.

There is currently an issue open on Slick's GitHub for this: https://github.com/slick/slick/issues/684

The sub-query approach you have in your answer would be the best approach.

Alternatively, I'd imagine you should be able to add support for the join-delete query in a custom Slick profile definition.