Joins become nested selects

Question

I've been using Slick and I've bumped into an issue I cannot seem to get around. What I'm trying to do is to join across multiple tables and Slick seems to directly translate the joins into nested selects, which I don't think are as efficient.

For example, this is the kind function I'm using to make the joins:

  def partialReviewByPlaceIds(q: ReviewQuery, placeIds: Long*)(implicit psDAO: PlaceStatusDAO, upDAO: UserProfileDAO) = for {
    ((r, up), ps) <- withUP(q) join psDAO.PlaceStatuses /*.filter(_.placeId.inSet(placeIds))*/ on {
      case ((r, _), ps) => r.providerPlaceId === ps.providerSpecId && ps.provider === r.provider
    } if ps.placeId.inSet(placeIds)
  } yield (r, up)

And withUP looks like:

  def withUP(q: ReviewQuery) = for {
    (r, a) <- q join upDAO.UserProfiles on ((r, u) => r.providerUserId === u.providerUserId && r.provider === u.provider)
  } yield (r, a)

And this translates directly to SQL:

SELECT
  x2.x3,
  x2.x4,
  x2.x5,
  x2.x6,
  x2.x7,
  x8.`url`,
  x2.x9,
  x2.x10,
  x8.`provider_user_id`,
  x2.x11,
  x8.`id`,
  x2.x12,
  x8.`provider`,
  x2.x13,
  x2.x14,
  x2.x15,
  x8.`name`,
  x2.x16,
  x8.`image_url`,
  x2.x17
FROM (SELECT
        `created`            AS x14,
        `done`               AS x10,
        `favourite`          AS x15,
        `url`                AS x5,
        `text`               AS x17,
        `provider`           AS x7,
        `provider_review_id` AS x4,
        `id`                 AS x16,
        `read`               AS x13,
        `provider_place_id`  AS x6,
        `rating`             AS x3,
        `provider_user_id`   AS x9,
        `flagged`            AS x12,
        `language`           AS x11
      FROM `review`
      ORDER BY `created` DESC) x2, `user_profile` x8, `place_status` x18
WHERE ((x18.`place_id` IN (17)) AND ((x2.x9 = x8.`provider_user_id`) AND (x2.x7 = x8.`provider`))) AND
      ((x2.x6 = x18.`provider_place_id`) AND (x18.`provider` = x2.x7))
LIMIT 0, 21

What would be a better way to approach this? (I haven't added the schema here since I don't think its necessarily required in the situation & can be figured out from looking at the query.)

EDIT when I edit the partialReviewByPlaceIds method to do everything in the for comprehension instead:

def partialReviewByPlaceIds(q: ReviewQuery, placeIds: Long*)(implicit psDAO: PlaceStatusDAO, upDAO: UserProfileDAO) = for {
    ((r, up), ps) <- Reviews join upDAO.UserProfiles on ((r, up) => r.providerUserId === up.providerUserId && r.provider === up.provider) join psDAO.PlaceStatuses /*.filter(_.placeId.inSet(placeIds))*/ on {
      case ((r, _), ps) => r.providerPlaceId === ps.providerSpecId && ps.provider === r.provider
    } if ps.placeId.inSet(placeIds)
  } yield (r, up)

I get a flatter query like:

SELECT
  x2.`favourite`,
  x3.`image_url`,
  x2.`provider_place_id`,
  x2.`id`,
  x2.`rating`,
  x2.`provider_user_id`,
  x2.`provider`,
  x3.`id`,
  x3.`provider_user_id`,
  x2.`done`,
  x2.`flagged`,
  x2.`language`,
  x3.`provider`,
  x2.`url`,
  x2.`text`,
  x2.`provider_review_id`,
  x2.`read`,
  x2.`created`,
  x3.`name`,
  x3.`url`
FROM `review` x2, `user_profile` x3, `place_status` x4
WHERE
  ((x4.`place_id` IN (17)) AND ((x2.`provider_user_id` = x3.`provider_user_id`) AND (x2.`provider` = x3.`provider`)))
  AND ((x2.`provider_place_id` = x4.`provider_place_id`) AND (x4.`provider` = x2.`provider`))
LIMIT 0, 21

It seems to me that Slick compiles queries to SQL as soon as it sees operations like sort and filter on the query – which makes it difficult to combine the queries together.

For example, one would expect to sort by review.created not in a subquery but in the join.

Play framework 2.5.8

Play-slick 2.0.2

Answer 1

Yes, your suspicion is correct - where you place sortBy / filter matters.

Instead of having full queries with filter / sortBy, you may need to extract them to separate methods and perhaps compose them method.

def partialReviewByPlaceIds(q: ReviewQuery, placeIds: Long*)(implicit psDAO: PlaceStatusDAO, upDAO: UserProfileDAO) = for {
    ((r, up), ps) <- Reviews join upDAO.UserProfiles on ((r, up) => matchingFilters(r, up)) join psDAO.PlaceStatuses /*.filter(_.placeId.inSet(placeIds))*/ on {
      case ((r, _), ps) => r.providerPlaceId === ps.providerSpecId && ps.provider === r.provider
    } if ps.placeId.inSet(placeIds)
  } yield (r, up)


 def matchingFilter(r: Reviews, up: UserProfiles): Rep[Boolean] = {
    r.providerUserId === up.providerUserId && r.provider === up.provider
 }

I'd also suggest to full monadic notation (for joins) to untangle this query a little bit:

def partialReviewByPlaceIds(q: ReviewQuery, placeIds: Long*)(implicit psDAO: PlaceStatusDAO, upDAO: UserProfileDAO) = for {
    r <- Reviews
    up <-  upDao.UserProfiles if matchingFilters(r, up)
    ps <- psDAO.PlaceStatuses if r.providerPlaceId === ps.providerSpecId && ps.provider === r.provider && ps.placeId.inSet(placeIds)
} yield (r, up)

def matchingFilter(r: Reviews, up: UserProfiles): Rep[Boolean] = {
    r.providerUserId === up.providerUserId && r.provider === up.provider
}

You could also extract these join conditions to foreinKeys - which should make it even more concise and reusable, e.g.

def profile = foreignKey("fk_review_profile", (providerUserId, provider), UserProfiles)(p => (p.providerUserId, p.provider))

then you would have:

def partialReviewByPlaceIds(q: ReviewQuery, placeIds: Long*)(implicit psDAO: PlaceStatusDAO, upDAO: UserProfileDAO) = for {
    r <- Reviews
    up <-  r.profile
    ps <- psDAO.PlaceStatuses if r.providerPlaceId === ps.providerSpecId && ps.provider === r.provider && ps.placeId.inSet(placeIds)
} yield (r, up)

You could do the same with 2nd join. Hope this would help you with composing queries a little bit differently (to avoid these nested joins).