Slick nested outer joins with many-to-many tables

Question

I'm stuck with a slick query and I unfortunately can't find a similar example.

Config:

scalaVersion := "2.11.7"
libraryDependencies += "com.typesafe.play" %% "play-slick" % "2.1.0"

Heres the scenario. I have a table/model that's called Record. The object itself holds two sequences, namely Tags and Markets. This is a represenational image of the database structure (I'm aware of the fact that this is not an ER-diagram, it's not meant to be):

Tags and Markets have their own tables and are connected to Record by many-to-many relations. The goal is to build a query that retrieves all records (regardless of tags and markets), records with markets and records with tags. I had something like this in mind:

Future[Seq[(RecordModel, Option[Seq[MarketModel]], Option[Seq[TagModel]])]]

So this is what I have:

def myFunction(): Future[Seq[(RecordModel, Seq[MarketModel], Seq[TagModel])]] = {
  val query = for {
    recs <- records joinLeft (recordsMarkets join markets on (_.marketId === _.marketId)) on (_.recordId === _._1.recordId) joinLeft (recordsTags join tags on (_.tagId === _.tagId)) on (_._1.recordId === _._1.recordId)
  } yield recs
  db.run(query.result).map(_.toList.groupBy(_._1).map {
    case (r, m) => (
      r._1, // Records
      r._2.groupBy(_._2).toSeq.map { case (a, b) => a }, // Markets
      t.flatMap(_._2.groupBy(_._2).map { case (t, relation) => t }) // Tags
    )
  }.toSeq)
}

I'm not sure, if I'm on the right path here. It seems like it's almost what I want. This function will only return Records with Markets and Tags rather than having them as optional.

I can't wrap my head around this. There don't seem to be any comprehensive examples of such complex queries anywhere. Any help is greatly appreciated. Thanks in advance!

Answer 1

I finally had the time to focus on this issue again. With my current architecture and structure, I couldn't implement @Valerii Rusakov's answer, but it helped to solve the problem tremendously. Thank you!

So here's how I did it:

def myFunction: Future[Seq[(RecordModel, Seq[Option[(TagsModel, Record_TagsModel)]], Seq[Option[(MarketsModel, Record_MarketModel)]], Seq[Option[(UrlsModel, Record_UrlModel)]])]] = {
val query = for {
  (((records, tags), markets), urls) <- (records filter (x => x.deleted === false && x.clientId === 1)
    joinLeft (tags join recordTags on (_.tagId === _.tagId)) on (_.recordId === _._2.recordId)
    joinLeft (markets join recordMarkets on (_.marketId === _.marketId)) on (_._1.recordId === _._2.recordId)
    joinLeft (urls join recordUrls on (_.urlId === _.urlId)) on (_._1._1.recordId === _._2.recordId))
} yield (records, tags, markets, urls)
db.run(query.result).map(_.toList.groupBy(_._1).map { // Group by records
  case (records, composedResult) =>
    (
      records,
      composedResult.groupBy(_._2).keys.toSeq, // Tags and RecordTags
      composedResult.groupBy(_._3).keys.toSeq, // Markets and RecordMarkets
      composedResult.groupBy(_._4).keys.toSeq // Urls and RecordUrls
    )
}.toSeq)
 }

Notice that I yield for (((records, tags), markets), urls). This allows me to access those precise attributes later on, which makes the grouping and mapping a lot easier. It's still not perfect because I have to work with the table and the relational table e.g. TagsModel, Record_TagsModel. This is just a minor issue though. Maybe some of you guys know how to resolve it. The current function returns all records regardless of tags, markets or urls.