scala – 具有多对多表的Slick嵌套外连接

发布时间：2020-12-16 08:46:33 所属栏目：安全来源：网络整理

导读：我被困在一个光滑的查询,但遗憾的是我找不到类似的例子. 配置： scalaVersion := "2.11.7"libraryDependencies += "com.typesafe.play" %% "play-slick" % "2.1.0" 继承人的情景.我有一个名为Record的表/模型.对象本身包含两个序列,即标签和市场.这是数据库

我被困在一个光滑的查询,但遗憾的是我找不到类似的例子.

配置：

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

继承人的情景.我有一个名为Record的表/模型.对象本身包含两个序列,即标签和市场.这是数据库结构的代表性图像(我知道这不是ER图,它并不意味着)：

enter image description here

标签和市场有自己的表格,并通过多对多关系连接到记录.目标是构建一个查询,检索所有记录(无论标签和市场),市场记录和带标签的记录.我有这样的想法：

未来[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)
}

如果我走在正确的道路上,我不确定.看起来这几乎就是我想要的.此功能仅返回带有市场和标签的记录,而不是将它们作为可选项.

我无法绕过这个.在任何地方似乎都没有任何关于此类复杂查询的综合示例.任何帮助是极大的赞赏.提前致谢！

解决方法

你正确的方式.假设您的光滑映射定义为：

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.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.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",marketId))
  foreignKey("record_market_record_fk",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)

这是通过使用PostgreSQL配置文件进行以下SQL转换：

select
   x2."id",x3."id",x4."record_id",x4."tag_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])]]

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!