3

Is there any way to create nested select using slick 3.2+ ? Basically all that I need described here How to write nested queries in select clause

However on slick 3.2 this approach does not work.

smaiakov
  • 470
  • 5
  • 20

1 Answers1

1

If you have tables Users (id: UUID, email: String) and Persons (userId: UUID, name: String, surname: String) than query

select email
from Users
where id in (select userId
             from Persons
             where name = 'John'
             and surname = 'Smith')

would look kind of like:

users
  .filter(
    _.id in persons
              .filter(p => p.name === "John" && p.surname === "Smith")
              .map(_.userId)
  )
  .map(_.email)
  .result

Things you need to remember:

  • Query type is not DBIO (nor DBIOAction) - if you want to compose queries, you need to do it before calling .result on them
  • when you are using subquery in condition use in instead of inSet

Same principle should hold whether you use in, join, etc.

Mateusz Kubuszok
  • 24,995
  • 4
  • 42
  • 64
  • Thanks but I need nested select ie. select p.1, (select a.2 from tableA a where p.id = a.id ) from tableA as p – smaiakov Jul 10 '18 at 09:40
  • You can rewrite it: `( tableA.map(a => (a.id, a.2)) join tableB.map(b.id, b.1) on (_._1 === _._1) ).map(_._4)`. Alternatively, just use `sql"""query"""`. – Mateusz Kubuszok Jul 10 '18 at 11:02