使用多对多表进行嵌套外连接的Slick操作

Question

我遇到了一些困难，写了一个高效的查询语句，但是不幸的是我找不到类似的例子。

配置：

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

这是一个场景。我有一个名为“Record”的表/模型。对象本身包含两个序列，即“Tags”和“Markets”。这是数据库结构的表示形式(我知道这不是ER图，它不是为此而设计的)： “Tags”和“Markets”有自己的表，并通过多对多关系与“Record”相连接。目标是构建一个检索所有记录(不考虑标记和市场)、记录市场和记录标记的查询。我想到了这样的方式： Future[Seq[(RecordModel, Option[Seq[MarketModel]], Option[Seq[TagModel]])]] 所以这就是我拥有的内容：

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

我不确定我是否在正确的路上。看起来它几乎是我想要的。这个函数只会返回带有Markets和Tags的Records，而不是将它们作为可选项。

我无法理解这个。似乎没有任何关于这种复杂查询的全面示例。非常感谢您的帮助。提前致谢！

- Nocebo

---

它根本不符合关系模型。集合不能作为字段类型。 - Epicurist

我知道，那不是一个ER图。这些字段并没有存储在数据库中，只是一个视觉键。为什么你会对此如此纠结呢？这与问题和提问并没有太大关系。因为额外的图片不符合您喜欢的ER表示法而对问题进行投票反对并没有帮助... - Nocebo

甚至对我的答案进行了负评。如果尝试在错误的领域做事情，很抱歉它永远不会奏效。 - Epicurist

@Epicurist 你在说什么？你刚刚给我14个答案和问题点了踩吗？你怎么了？ - Nocebo

Answer 1

你正在走向正确的方向。假设您定义了一个简洁的映射:

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

为了连接具有多对多关系的表，您应该使用左连接和内连接相结合的方式进行如下操作：

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)

这是由Slick转换为PostgreSQL配置文件的SQL语句：

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"

最后一步是将此查询的结果正确地映射到 scala 类中。我是这样做的：

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

这将返回Future[Seq[(RecordRow, Seq[TagRow], Seq[MarketRow])]]。

- Valerii Rusakov

Answer 2

我终于有时间再次专注于这个问题。根据我的架构和结构，我无法实现 @Valerii Rusakov 的答案，但它对解决问题有很大帮助。谢谢！

下面是我如何解决它的方法：

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

请注意，我使用(((records, tags), markets), urls)来进行yield。这样做可以让我以后更容易地访问这些属性，从而使分组和映射变得更加简单。但它仍然不完美，因为我必须同时处理表格和关系表格，例如TagsModel，Record_TagsModel。尽管如此，这只是一个小问题，也许你们中的一些人知道如何解决它。当前函数返回所有的records，而不考虑tags、markets或urls。

- Nocebo