Slick join with subqueries

Question

I build following SQL Query

SELECT
v.uuid, d.start_time, d.end_time
FROM
visits v
  INNER JOIN
  visit_dates d ON v.uuid = d.visit_uuid
WHERE
v.study_environment_site_uuid = (SELECT
  study_environment_site_uuid
  FROM
  visits
  WHERE
  uuid = 'e4663612-39f9-4c43-bd86-c4c5a9235b03')
AND v.uuid != 'e4663612-39f9-4c43-bd86-c4c5a9235b03'
AND d.start_time < (SELECT start_time FROM visit_dates WHERE visit_uuid = 'e4663612-39f9-4c43-bd86-c4c5a9235b03' ORDER BY start_time LIMIT 1)
ORDER BY d.start_time;

now trying to reflect that into Slick

(for {
  vSes <- visits.filter(_.uuid === uuid)
  vDate <- visitDates.filter(_.visitUuid === uuid).sortBy(_.startTime).take(1)
  (v, d) <- visits join visitDates on (_.uuid === _.visitUuid)
  if (v.uuid =!= uuid && v.studyEnvironmentSiteUuid === vSes.studyEnvironmentSiteUuid && d.startTime < vDate.startTime)
} yield (v.uuid)).result.map(_.headOption)

But this produced wrong result. I am using Slick 3.2.1 Following SQL is generated

SELECT 
    x2.`uuid`,
    x7.start_time
FROM
    `visits` x3,
    (SELECT 
        `visit_uuid` AS x4, `start_time` AS x5
    FROM
        `visit_dates`
    WHERE
        `visit_uuid` = ?
    ORDER BY `start_time`
    LIMIT 1) x6,
    `visits` x2,
    `visit_dates` x7
WHERE
    ((x2.`uuid` = x7.`visit_uuid`)
        AND ((NOT (x2.`uuid` = ?))
        AND (x7.`start_time` < x6.x5)))
        AND ((x3.`uuid` = ?)
        AND (x2.`study_environment_site_uuid` = x3.`study_environment_site_uuid`));

Generated query is not a join and returns multiple rows instead of one that the manual query produces.

Any ideas/pointers?

rojanu rojanu · Accepted Answer · 2017-09-01T08:33:39

As there isn't an answer yet, I will post my workaround for the problem

for {
  (v, d) <- visits join visitDates on (_.uuid === _.visitUuid)
  vSes <- visits.filter(_.uuid === uuid)
  vDate <- visitDates.filter(_.visitUuid === uuid).sortBy(_.startTime).take(1)
  if v.uuid =!= uuid && v.studyEnvironmentSiteUuid === vSes.studyEnvironmentSiteUuid && d.startTime < vDate.startTime
} yield (v.uuid, d.startTime)).result.map {
  case results@(_ +: _) => Some(results.maxBy(_._2)._1)
  case _ => None
}

Slick join with subqueries

1 Answers