Executing non-database actions in a transaction in

Question

I'm having trouble understanding the new Slick DBIOAction API, which does not seem to have a lot of examples in the docs. I am using Slick 3.0.0, and I need to execute some DB actions and also some calculations with the data received from the database, but all of those actions have to be done inside a single transaction. I'm trying to do the following:

1. Execute a query to database (the types table).
2. Do some aggregations and filtering of the query results (this calculation can't be done on the database).
3. Execute another query, based on the calculations from step 2 (the messages table — due to some limitations, this query has to be in raw SQL).
4. Join data from step 2 and 3 in memory.

I want the queries from step 1 and 3 to be executed inside a transaction, as the data from their result sets has to be consistent.

I've tried to do this in a monadic join style. Here's an overly simplified version of my code, but I can't even get it to compile:

  val compositeAction = (for {
    rawTypes <- TableQuery[DBType].result
    (projectId, types) <- rawTypes.groupBy(_.projectId).toSeq.map(group => (group._1, group._2.slice(0, 10)))
    counts <- DBIO.sequence(types.map(aType => sql"""select count(*) from messages where type_id = ${aType.id}""".as[Int]))
  } yield (projectId, types.zip(counts))).transactionally

1. The first row of for comprehension selects the data from the types table.
2. The second row of for comprehension is supposed to do some grouping and slicing of the results, resulting in a Seq[(Option[String], Seq[String])]
3. The third row of for comprehension has to execute a set of queries for every element from the previous step, in particular, it has to execute a single SQL query for each of the values inside Seq[String]. So in the third row I build a sequence of DBIOActions.
4. The yield clause zips types from the second step and counts from the third step.

This construction, however, does not work and gives two compile time errors:

Error:(129, 16) type mismatch;
 found   : slick.dbio.DBIOAction[(Option[String], Seq[(com.centreit.proto.repiso.storage.db.models.DBType#TableElementType, Vector[Int])]),slick.dbio.NoStream,slick.dbio.Effect]
    (which expands to)  slick.dbio.DBIOAction[(Option[String], Seq[(com.centreit.proto.repiso.storage.db.models.TypeModel, Vector[Int])]),slick.dbio.NoStream,slick.dbio.Effect]
 required: scala.collection.GenTraversableOnce[?]
        counts <- DBIO.sequence(types.map(aType => sql"""select count(*) from messages where type_id = ${aType.id}""".as[Int]))
               ^
Error:(128, 28) type mismatch;
 found   : Seq[Nothing]
 required: slick.dbio.DBIOAction[?,?,?]
        (projectId, types) <- rawTypes.groupBy(_.projectId).toSeq.map(group => (group._1, group._2.slice(0, 10)))
                           ^

I've tried to wrap the second line in a DBIOAction by using DBIO.successful, which is supposed to lift a constant value into the DBIOAction monad:

(projectId, types) <- DBIO.successful(rawTypes.groupBy(_.projectId).toSeq.map(group => (group._1, group._2.slice(0, 10))))

But in this code the types variable is inferred to be Any, and the code does not compile because of that.

Answer 1

Try it this way :

val compositeAction = (for {
  rawTypes <- TableQuery[DBType].result
  pair <- DBIO.sequence(rawTypes.groupBy(_.projectId).toSeq.map(group => DBIO.successful(group)))
  counts <- DBIO.sequence(pair.head._2.map(aType => sql"""select count(*) from messages where type_id = ${aType.id}""".as[Int]))
} yield (pair.head._1, pair.head._2.zip(counts))).transactionally