GreenAsJade GreenAsJade - 3 months ago 16
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 === _.id) joinLeft
Addresses on (_._2.addressId === _.id) joinLeft
Businesses on (_._1._2.businessId === _.id) joinLeft
Addresses on (_._2.flatMap(_.addressId) === _.id)
}yield(p, a, b, ba)

db.run(q.result.headOption).map{ _.map{case(p,a,b,ba) =>
val business = b match {
case Some(b) => Some(dtos.Business(b.name, b.abn, b.adminFee, ba, b.id))
case _ => None
}

dtos.ProfileInfo(p, a, business)
}}
}


I've included the result processing (
db.run(...)
) 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.

Notes:


  • 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(_.id === userId
    )

  • The equivalent SQL is:

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


Answer

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.id && p.addressId === a.flatMap(_.id) && p.businessId === b.flatMap(_.id) && b.flatMap(_.addressId) === ba.id
} 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 === p.id
} joinLeft Addresses on {
  case ((u, p), a) => p.addressId === a.id
} joinLeft Businesses on {
  case (((u, p), a), b) => p.businessId === b.id
} joinLeft Addresses on {
  case ((((u, p), a), b), ba) => b.flatMap(_.addressId) === ba.id
} 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.