你正在走向正确的方向。假设您定义了一个简洁的映射:
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)
.mapValues(values =>values.map(value => (value._1._2.map(_._2), value._2.map(_._2))))
.map(mapEntry =>(mapEntry._1, mapEntry._2.flatMap(_._1), mapEntry._2.flatMap(_._2)))
.toSeq
})
}
这将返回
Future[Seq[(RecordRow, Seq[TagRow], Seq[MarketRow])]]
。