Explicit joins and nested queries

39 views
Skip to first unread message

Dan Gravell

unread,
Oct 10, 2017, 9:33:44 AM10/10/17
to Slick / ScalaQuery
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

virtualeyes

unread,
Oct 13, 2017, 6:57:29 PM10/13/17
to Slick / ScalaQuery
Joins are, IMO, a weak point in the library, both in terms of syntax and generated sql.

If at all possible upgrade to MySQL 5.7 (to offset the subselect cost). That, or drop down to sql interpolation:
sql"select ... from t join ... on ... left join ..."

Good luck.

Dan Gravell

unread,
Oct 16, 2017, 4:00:17 AM10/16/17
to scala...@googlegroups.com
Thanks. That's exactly what I've done (change to SQL interpolation).

Dan


bliss - fully automated music library management. Read the music library management blog.

--

---
You received this message because you are subscribed to a topic in the Google Groups "Slick / ScalaQuery" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/scalaquery/J3pM5NBDW7U/unsubscribe.
To unsubscribe from this group and all its topics, send an email to scalaquery+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/scalaquery/705f2da2-5c53-4c58-8ff8-24c3f9f5109f%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Reply all
Reply to author
Forward
0 new messages