Slick nested outer joins with many-to-many tables
Asked Answered
P

2

6

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!

Parry answered 19/12, 2018 at 9:11 Comment(4)
It simply doesn't comply to the relational model. A collection cannot be a field-type.Waly
Again, I know. Again, it's not a ER-Diagram. Those fields are not stored in the database. Again, it's just a visual key. Why do you get hung up on this so much? This has not really much to do with the problem and the question. Downvoting the question because an additional image doesn't apply to your prefered ER-notation is not helpful...Parry
Downvoting my answer even. I am sorry, if try to things in the wrong domain it never work.Waly
@Waly what are you talking about? Did you just downvote 14 answers and questions of mine? What is wrong with you?Parry
P
3

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.

Parry answered 21/6, 2019 at 9:46 Comment(0)
M
4

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])]]

Medora answered 28/12, 2018 at 10:10 Comment(0)
P
3

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.

Parry answered 21/6, 2019 at 9:46 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.