How to write nested queries in select clause with slick 3.2 +

Question

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.

Mateusz Kubuszok · Accepted Answer · 2018-07-09 17:37:49Z

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.

answered Jul 9, 2018 at 17:37

Mateusz Kubuszok

27.9k4 gold badges47 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

smaiakov Over a year ago

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

Mateusz Kubuszok Over a year ago

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""".

Collectives™ on Stack Overflow

How to write nested queries in select clause with slick 3.2 +

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related