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?
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)