GreenAsJade GreenAsJade - 1 year ago 89
Scala Question

How to write readable nested join queries with Slick 3.0

This code is creating a query for use in retrieving a user's profile on a web back end. It creates a query that assembles the necessary information into a DTO (which is just a case class) that is subsequently sent back as JSON.

def getProfile(userId: Long)={
val q = for{
((((u,p),a),b), ba) <- filterById(userId) join
People on (_.personId === joinLeft
Addresses on (_._2.addressId === joinLeft
Businesses on (_._1._2.businessId === joinLeft
Addresses on (_._2.flatMap(_.addressId) ===
}yield(p, a, b, ba){{case(p,a,b,ba) =>
val business = b match {
case Some(b) => Some(dtos.Business(, b.abn, b.adminFee, ba,
case _ => None

dtos.ProfileInfo(p, a, business)

I've included the result processing (
) for context only.

I'm looking for a more readable way to express the query construction.

My experience reading this is "Wait, what?? ...
on (_._1._2.flatMap(_.addressId)
.... what is that doing?? Why flatmap there and not here:
on (_._1._2.businessId
. These are actually straight forwards things, but don't read at all straight fowards.

I'm looking for a way of expressing this that doesn't require the amount of deduction needed to read this version. I have to "deduce" what _._1._2 is, and why it needs to be flattened, which I don't have to do with the equivalent SQL.


  • This code comes from an existing application (not written by me) which I am extending.

  • Users, People, Addresses, Businesses are (obviously?) the tables.

  • People and Businesses have Addresses.

  • Users have a Person(*), People have a Business

  • filterByUserId(userId)
    is basically equivalent to
    Users.filter( === userId

  • The equivalent SQL is:

    select p.*, a1.*, b.*, a2.* from Users u
    innerJoin People p on (u.personId ==
    leftJoin Addresses a1 on (p.addressId ==
    leftJoin Businesses b on (p.businessId ==
    leftJoin Addresses a2 on ( b.addressId ==

Answer Source

You should experiment with something like this:

val q = Users join People joinLeft Addresses joinLeft Businesses joinLeft Addresses on {
  case ((((u, p), a), b), ba) => u.personId === && p.addressId === a.flatMap( && p.businessId === b.flatMap( && b.flatMap(_.addressId) ===
} map {
  case ((((u, p), a), b), ba) => (p, a, b, ba)

The other solution would be to make joins without using for comprehension, so you wouldn't have to use underscores to extract values from tuples:

val q = Users join People on {
  case (u, p) => u.personId ===
} joinLeft Addresses on {
  case ((u, p), a) => p.addressId ===
} joinLeft Businesses on {
  case (((u, p), a), b) => p.businessId ===
} joinLeft Addresses on {
  case ((((u, p), a), b), ba) => b.flatMap(_.addressId) ===
} map {
  case ((((u, p), a), b), ba) => (p, a, b, ba)

You haven't provided full definitions of your data so I wasn't able to fully test those solutions, but this should give you some insight into a different way of defining joins in Slick. Let me know if this was helpful at all.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download