I'm a bit worried I've missed something, because I see some old threads on this subject but with issues closed that they are fixed now... I'm using the latest Slick, 3.2.1.
Running on MySQL 5.6.35 btw.
I have a join over five tables to do. Here's how it should appear in SQL:
SELECT x2.`SOURCE_URI`, x2.`NAME`, x2.`COUNTRY`, x3.`ALIAS`, x4.`URL`
FROM `ARTIST` x2
LEFT JOIN ARTIST_ALIAS x3 ON x3.SOURCE_URI=x2.SOURCE_URI
LEFT JOIN ARTIST_URL x4 ON x2.`SOURCE_URI` = x4.`SOURCE_URI`
LEFT JOIN ARTIST_IMAGE x5 ON x2.`SOURCE_URI`=x5.`SOURCE_URI`
JOIN ARTIST_CALLED x6 ON x2.`SOURCE_URI` = x6.`SOURCE_URI` AND x6.`CALLED` = 'nirvana';
I use left joins because some of the child rows are optional. This completes in 0.3s.
So in Slick, here's what I've written:
override def query() = {
val joined = artists//.filter(artistFilter)
.joinLeft(artistAliases).on {
case (artist, artistAlias) => artist.sourceUri===artistAlias.sourceUri
}
.joinLeft(artistUrls).on {
case ((artist, artistAlias), artistUrl) => artist.sourceUri===artistUrl.sourceUri
}
.joinLeft(artistImages).on {
case (((artist, artistAlias), artistUrl), artistImage) => artist.sourceUri===artistImage.sourceUri
}
.join(artistCalleds).on{
case ((((artist, artistAlias), artistUrl), artistImage), artistCalled) => artist.sourceUri===artistCalled.sourceUri && artistCalled.called===name.name }
val results = for {
((((artist, alias), url), img), called) <- joined
} yield (artist.sourceUri, artist.name, artist.country, alias.flatMap(_.alias), url.flatMap(_.url), img.flatMap(_.uri), img.flatMap(_.width), img.flatMap(_.height), img.flatMap(_.`type`), img.flatMap(_.score)) results.result
}
So basically it creates a Tuple of some basic information then stuff from the optional rows.
Trouble is, the SQL command it creates is far less performant:
SELECT x2.x3,
x2.x4,
x2.x5, (CASE
WHEN (x2.x6 IS NOT NULL) THEN x2.x7
ELSE NULL
END), (CASE
WHEN (x2.x8 IS NOT NULL) THEN x2.x9
ELSE NULL
END), (CASE
WHEN (x10.`ID` IS NOT NULL) THEN x10.`URI`
ELSE NULL
END), (CASE
WHEN (x10.`ID` IS NOT NULL) THEN x10.`WIDTH`
ELSE NULL
END), (CASE
WHEN (x10.`ID` IS NOT NULL) THEN x10.`HEIGHT`
ELSE NULL
END), (CASE
WHEN (x10.`ID` IS NOT NULL) THEN x10.`TYPE`
ELSE NULL
END), (CASE
WHEN (x10.`ID` IS NOT NULL) THEN x10.`SCORE`
ELSE NULL
END)
FROM
(SELECT x11.x12 AS x5,
x11.x13 AS x14,
x11.x15 AS x3,
x11.x16 AS x4,
x11.x17 AS x18,
x11.x19 AS x6,
x11.x20 AS x21,
x11.x22 AS x7,
(CASE
WHEN (x23.`SOURCE_URI` IS NULL) THEN NULL
ELSE 1
END) AS x8,
x23.`SOURCE_URI` AS x24,
x23.`URL` AS x9
FROM
(SELECT x25.`COUNTRY` AS x12,
x25.`SORT_NAME` AS x13,
x25.`SOURCE_URI` AS x15,
x25.`NAME` AS x16,
x25.`TYPE` AS x17,
(CASE
WHEN (x26.`SOURCE_URI` IS NULL) THEN NULL
ELSE 1
END) AS x19,
x26.`SOURCE_URI` AS x20,
x26.`ALIAS` AS x22
FROM `ARTIST` x25
LEFT OUTER JOIN `ARTIST_ALIAS` x26 ON x25.`SOURCE_URI` = x26.`SOURCE_URI`) x11
LEFT OUTER JOIN `ARTIST_URL` x23 ON x11.x15 = x23.`SOURCE_URI`) x2
LEFT OUTER JOIN `ARTIST_IMAGE` x10 ON x2.x3 = x10.`SOURCE_URI`
LEFT OUTER JOIN `ARTIST_CALLED` x27 ON (x2.x3 = x27.`SOURCE_URI`) AND (x27.`CALLED` = 'nirvana')
I just stopped it executing after a couple of minutes.
Now I can accept all the "case" stuff - I guess that's something to do with the optionals and optional rows. But why are subqueries generated? What have I done wrong in my Slick code?
Dan