Slick: two queries, one transaction

1
votes

Let's say I have two tables:

people:

id: long | uuid: UUID     | name: String

----

cars

id: long | owner_id: long | name: String

Where owner is a foreign key to people.id.

I have a query that selects all owner cars by owner's uuid (just an API design).

The question is: how can I execute the following queries in one transaction where I have to get people.id by its uuid (SELECT id from people where uuid = ?) and then SELECT * from cars where owner_id = id?

Of course, I can get the id from people table by its uuid at first, like

val ownerId = db run people.filter(_.uuid === uuid.bind).map(_.id).result.head

And then run another query to select cars by owner_id:

val cars = ownerId.flatMap { 
  id => db run cars.filter(_.ownerId === id).result 
}

But this will not happen in one transaction. Any thoughts?

2
scalatransactionsslick

2 Answers

3
votes

There is a transactionally method for the slick actions. You should use:

import slick.driver.PostgresDriver.api._  // for example

val query = for {
  p <- peoples.filter(_.uuid === "some-id")
  c <- cars.filter(_.owner_id === p.uuid)
} yield (p, c)

val action = query.result
val transactionalAction = action.transactionally // will be executed 
                                                 // in single transaction

// Ungrouped result
// TODO: You can group on db side by your query or 
// on client side using groupBy from collection API
val result:Future[Seq[(People, Cars)]] = db.run(transactionalAction)
0
votes

I'd say :

val groupByQuery =  cars.join(people)
 .on (_.owner_id === _.id)
 .map{
  case (car, owner) => (owner.uuid, car)
 }
.groupBy(_._1)

db.run(groupByQuery)