Scala+Slick 3: Inserting the result of one query into another table

7
votes

This question is about slick 3.0 or 3.1 (I am flexible about that)

I have an intermediate query which I process with map, for, etc. to obtain the result I want. In the end I have a 

val foo: DBIOAction[Seq[MySchema.Bar], NoStream, Effect.Read]

Now I have a val bar: TableQuery[MySchema.Bar] and I want to insert foo in to it.

If foo would be a Seq I could simply do bar ++= foo, but it is not.

The only way I found is to materialize the result by awaiting it. Like this

val query = (bar ++= Await.result(db.run(foo), Duration.Inf))

Obviously query needs to be run at some point with db.run. But now I have two DB-runs. Wouldn't it be better to have everything in a single run?

Is there a better way to do this?

2
scalaslickslick-3.0
bar += foo ? But you would still have to db.run it regardless of whether it's += or ++=... (?) – kornfridge
I think ++= with side-effects was the slick2 api, while the slick3 is more functional, so it requires you to explicitly do a db.run – kornfridge
Yes the result of ++= is now a query that needs to be run. I edited the question in this point. But I have to do two db.run calls for this task. – masgo
Possible duplicate of Compose "Insert...Select...Where" query – Eugene Lopatkin

2 Answers

5
votes

DBIOAction has map/flatMap functions, so you can write something like

val insertAction = foo.flatMap(bar ++= _)

The insertAction will be of type DBIOAction[Int, NoStream, Effect.Write] or something like that (I'm not entirely sure about Int and the effect), then you can run it on the DB as any DBIOAction using db.run; what you then get is a future of the overall query and insert result.

3
votes

The questions was already answered but I came here looking for a different solution, so maybe this will be helpful to other people.

As @Aldo says, we want to work at the DBIO level as far as possible, but I would go further and say you should work at the Query level as far as possible, as this compiles to a single sql statement that can be sent to the database.

For example, an insert from a select should be compile to a INSERT INTO table1 SELECT.... If you use multiple DBIOS using flatMap, like suggested, this will be compiled into a SELECT, the values will be brought to memory and then an INSERT statement will be compiled, interpolating the values in a string and this new query will be sent to the database. In reality this can be much slower if your select returns many results, and it can drain your memory in the worst case.

So to compile something like this into a single query you can write:

val bar: TableQuery[MySchema.Bar]

val foo: Query[MySchema.Bar, Bar, Seq]

val insert: DBIO[Int] = bar.forceInsertAll(foo)