Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Slick nested outer joins with many-to-many tables

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): enter image description here

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!

like image 673
Nocebo Avatar asked Dec 19 '18 09:12

Nocebo


1 Answers

You are on right way. Suppose your slick mapping defined as:

case class RecordRow(id: Int)

case class TagRow(id: Int)
case class RecordTagRow(recordId: Int, tagId: Int)

case class MarketRow(id: Int)
case class RecordMarketRow(recordId: Int, marketId: Int)

class RecordTable(_tableTag: Tag)
    extends Table[RecordRow](_tableTag, "record") {
  val id = column[Int]("id", O.PrimaryKey, O.AutoInc)

  override def * = id <> ((id: Int) => RecordRow(id), RecordRow.unapply)
}

class TagTable(_tableTag: Tag) extends Table[TagRow](_tableTag, "tag") {
  val id = column[Int]("id", O.PrimaryKey, O.AutoInc)

  override def * = id <> ((id: Int) => TagRow(id), TagRow.unapply)
}

class RecordTagTable(_tableTag: Tag)
    extends Table[RecordTagRow](_tableTag, "record_tag") {
  val recordId = column[Int]("record_id")
  val tagId = column[Int]("tag_id")

  val pk = primaryKey("record_tag_pkey", (recordId, tagId))
  foreignKey("record_tag_record_fk", recordId, RecordQuery)(r => r.id)
  foreignKey("record_tag_tag_fk", tagId, TagQuery)(r => r.id)

  override def * =
    (recordId, tagId) <> (RecordTagRow.tupled, RecordTagRow.unapply)
}

class MarketTable(_tableTag: Tag)
    extends Table[MarketRow](_tableTag, "market") {
  val id = column[Int]("id", O.PrimaryKey, O.AutoInc)

  override def * = id <> ((id: Int) => MarketRow(id), MarketRow.unapply)
}

class RecordMarketTable(_tableTag: Tag)
    extends Table[RecordMarketRow](_tableTag, "record_market") {
  val recordId = column[Int]("record_id")
  val marketId = column[Int]("market_id")

  val pk = primaryKey("record_tag_pkey", (recordId, marketId))
  foreignKey("record_market_record_fk", recordId, RecordQuery)(r => r.id)
  foreignKey("record_market_market_fk", marketId, MarketQuery)(r => r.id)

  override def * =
    (recordId, marketId) <> (RecordMarketRow.tupled, RecordMarketRow.unapply)
}

val RecordQuery = new TableQuery(tag => new RecordTable(tag))
val TagQuery = new TableQuery(tag => new TagTable(tag))
val RecordTagQuery = new TableQuery(tag => new RecordTagTable(tag))
val MarketQuery = new TableQuery(tag => new MarketTable(tag))
val RecordMarketQuery = new TableQuery(tag => new RecordMarketTable(tag))

To join tables with many-to-many relations you should combine left joins with inner joins in this way:

val recordsQuery = RecordQuery
      .joinLeft(RecordTagQuery.join(TagQuery).on(_.tagId === _.id)).on(_.id === _._1.recordId)
      .joinLeft(RecordMarketQuery.join(MarketQuery).on(_.marketId === _.id)).on(_._1.id === _._1.recordId)

This is translated by slick into following SQL with PostgreSQL profile:

select
   x2."id",
   x3."id",
   x4."record_id",
   x4."tag_id",
   x3."id",
   x5."id",
   x6."record_id",
   x6."market_id",
   x5."id" 
from
   "record" x2 
   left outer join
      "record_tag" x4 
   inner join
      "tag" x3 
      on x4."tag_id" = x3."id" 
      on x2."id" = x4."record_id" 
   left outer join
      "record_market" x6 
   inner join
      "market" x5 
      on x6."market_id" = x5."id" 
      on x2."id" = x6."record_id"

The last step is to correctly map result of this query to scala classes. I've done it in this way:

db.run {
  recordsQuery.result
    .map(result => {
      result
        .groupBy(_._1._1) // RecordRow as a key
        .mapValues(values =>values.map(value => (value._1._2.map(_._2), value._2.map(_._2)))) // Seq[(Option[TagRow], Option[MarketRow])] as value
        .map(mapEntry =>(mapEntry._1, mapEntry._2.flatMap(_._1), mapEntry._2.flatMap(_._2)))  // map to Seq[(RecordRow, Seq[TagRow], Seq[MarketRow])]
        .toSeq
    })
}

This will return Future[Seq[(RecordRow, Seq[TagRow], Seq[MarketRow])]]

like image 145
Valerii Rusakov Avatar answered Oct 04 '22 02:10

Valerii Rusakov