Slick 3 many to many relations: how to get all the element of a table and their relations if they exist?

Question

I'm working with Slick 3 and Play! 2.4 and I have a very common problem that I don't manage to resolve.

I have a table playlist that can be linked to some tracks with the help of a relation table playlistsTracks. I want to be able to get all the playlists with their tracks relation and their tracks. My problem is that I don't manage to get the playlists if they do not have any relations.

Here are the 3 tables:

class Playlists(tag: Tag) extends Table[Playlist](tag, "playlists") {
    def id = column[Long]("playlistid", O.PrimaryKey, O.AutoInc)
    def name = column[String]("name")

    def * = (id.?, name) <> ((Playlist.apply _).tupled, Playlist.unapply)
  }

class PlaylistsTracks(tag: Tag) extends Table[PlaylistTrack](tag, "playliststracks") {
    def playlistId = column[Long]("playlistid")
    def trackId = column[UUID]("trackid")
    def trackRank = column[Double]("trackrank")

    def * = (playlistId, trackId, trackRank) <> ((PlaylistTrack.apply _).tupled, PlaylistTrack.unapply)

    def aFK = foreignKey("playlistId", playlistId, playlists)(_.id, onDelete = ForeignKeyAction.Cascade)
    def bFK = foreignKey("trackId", trackId, tracks)(_.uuid, onDelete = ForeignKeyAction.Cascade)
  }

class Tracks(tag: Tag) extends Table[Track](tag, "tracks") {
    def uuid = column[UUID]("trackid", O.PrimaryKey)
    def title = column[String]("title")

    def * = (uuid, title) <> ((Track.apply _).tupled, Track.unapply)
  }

For the moment the snippet of code that get the playlists look like this:

val query = for {
    playlist <- playlists
    playlistTrack <- playlistsTracks if playlistTrack.playlistId === playlist.id
    track <- tracks if playlistTrack.trackId === track.uuid
} yield (playlist, playlistTrack, track)

db.run(query.result) map { println }

It prints something like Vector(Playlist, PlaylistTrack, Track) (what I want) but I solely get the playlists having relations instead of getting all the playlists, even the one without relations as I would like.

I tried a lot of things like using join (or joinFull, joinLeft, joinRight...) but without success, and it is unfortunately difficult to find some example projects with not only very easy relations.

If you want all of the playlists then what should be "yielded" when you have a playlist value but no playlistTrack or track? In other words, do you want (playlist1, None, None) just playlist1...? — Ramón J Romero y Vigil, Oct 23 '15 at 16:35
Yes (playlist1, None, None) would be good: I would then map it into my PlaylistWithTracks case class (which looks like this: (playlist: Playlist, tracksWithRank: Seq[TrackWithRank]) — Simon, Oct 23 '15 at 16:40
The only solution I can think of is to do 3 separate queries and do the "joining" logic within the application (as opposed to server side). Is that acceptable? — Ramón J Romero y Vigil, Oct 23 '15 at 16:42
Not really, because, according to me, the queries would not be optimized by the database engine (PostgreSQL) with three separate queries. — Simon, Oct 23 '15 at 16:46
Hmm, I can't think of an sql query that would generate the type of result you are looking for. I'm not sure slick can do much if the underlying sql can't do it... — Ramón J Romero y Vigil, Oct 23 '15 at 16:57
Indeed, if there is no way to do it with a plain sql query, Slick won't do much and my question has no possible response, but I would have thought that it is possible to do a sql query generating this type of query. — Simon, Oct 23 '15 at 17:20

score 6 · Accepted Answer · answered Oct 23 '15 at 20:06

You need to use left join between the Playlists and PlaylistTracks table and use inner join between PlaylistTracks and Tracks. There are some things missing in the example so I can't actually compile the following, but I think you can try something like:

val query = for {
  (playlist, optionalPlaylistTrackAndTrack) <- playlists joinLeft (playlistsTracks join tracks on (_.trackId === _.uuid)) on (_.id === _._1.playlistId)
} yield (playlist, optionalPlaylistTrackAndTrack)

Note that optionalPlaylistTrackAndTrack is an Option of a tuple representing a playlist track and a track. This is because there may be a playlist without a playlistTrack.

It seems to perfectly suit my needs, and it compiled without any change ;) Thanks ! — Simon, Oct 23 '15 at 23:21

Slick 3 many to many relations: how to get all the element of a table and their relations if they exist?

1 Answers1