Scala/SLICK：三向联接不起作用 [英] Scala/Slick: 3 way join is not working

问题描述

我有两个表(姓名和电话)和另一个表组，它们有效地链接在一起(使用指向姓名和电话的外键)。我正在尝试查询姓名和电话号码，而某些姓名不需要电话号码。

val q = for {
  (n, (g, p)) <- names joinLeft groups on (_.id === _.nameId) join phones on (_.phoneId === _.id)
  // ABOVE LINE DOES NOT COMPILE !!!
} yield (n, p)

I Get"值phoneID不是(Contacts.NameTable，slick.lifted.Rep[Option[Contacts.GroupTable]])"

)的成员

以下是完整的源代码：

import scala.concurrent.Await
import scala.concurrent.duration._
import scala.concurrent.ExecutionContext.Implicits.global
import slick.driver.H2Driver.api._
import scala.util.Try
import scala.concurrent.Future

object Contacts extends App {

  val db = Database.forConfig("chapter03")

  def exec[T](program: DBIO[T]): T =
    Await.result(db.run(program), 5000 milliseconds)

  case class Name(firstName: String, lastName: String, id: Long = 0L)

  class NameTable(tag: Tag) extends Table[Name](tag, "Names") {

    def id = column[Long]("ID", O.PrimaryKey, O.AutoInc)
    def firstName = column[String]("FIRSTNAME")
    def lastName = column[String]("LASTNAME")

    override def * = (firstName, lastName, id) <> (Name.tupled, Name.unapply)
  }

  lazy val names = TableQuery[NameTable]

  case class Group(nameId: Long, phoneId: Long, id: Long = 0L)

  class GroupTable(tag: Tag) extends Table[Group](tag, "Groups") {

    def id = column[Long]("ID", O.PrimaryKey, O.AutoInc)
    def nameId = column[Long]("NAME")
    def phoneId = column[Long]("PHONE")

    override def * = (nameId, phoneId, id) <> (Group.tupled, Group.unapply)

    def name_fk = foreignKey("Groups_Names_ID_FK", nameId, names)(_.id, onUpdate = ForeignKeyAction.Cascade, onDelete = ForeignKeyAction.Cascade)
    def phone_fk = foreignKey("Groups_Phones_ID_FK", phoneId, phones)(_.id, onUpdate = ForeignKeyAction.Cascade, onDelete = ForeignKeyAction.Cascade)
  }

  lazy val groups = TableQuery[GroupTable]

  case class Phone(phoneNumber: String, id: Long = 0L)

  class PhoneTable(tag: Tag) extends Table[Phone](tag, "Phones") {

    def id = column[Long]("ID", O.PrimaryKey, O.AutoInc)
    def phoneNumber = column[String]("PHONE_NUMBER")

    override def * = (phoneNumber, id) <> (Phone.tupled, Phone.unapply)
  }

  lazy val phones = TableQuery[PhoneTable]

  def testNames = Seq(
    Name("George", "W"),
    Name("John", "A"),
    Name("Brad", "P"))

  def testGroups = Seq(
    Group(1L, 1L),
    Group(1L, 2L),
    Group(2L, 3L),
    Group(2L, 4L),
    Group(2L, 5L)
  )

  def testPhones = Seq(
    Phone("[phone]", 1L),
    Phone("[phone]", 1L),
    Phone("[phone]", 2L),
    Phone("[phone]", 2L),
    Phone("[phone]", 2L))

  case class Contact(firstName: String, lastName: String, phones: Seq[String])

  def populate: DBIOAction[Option[Int], NoStream,Effect.All] =  {
    for {    
      _ <- names.schema.drop.asTry andThen names.schema.create
      _ <- phones.schema.drop.asTry andThen phones.schema.create
      _ <- groups.schema.drop.asTry andThen groups.schema.create
      nameCount <- names ++= testNames
      phoneCount <- phones ++= testPhones
      groupCount <- groups ++= testGroups
    } yield nameCount
  }

  def getContacts: Future[Seq[Contact]] = {

    val q = for {
      (n, (g, p)) <- names joinLeft groups on (_.id === _.nameId) join phones on (_.phoneId === _.id)
// THIS LINE DOES NOT COMPILE !!!
    } yield (n, p)

    db.run(q.result).map { (row) =>
      row.groupBy(_._1).map { x =>
        val plist = x._2.map(_._2)
        if (!plist(0).isEmpty) {
          val phones: Seq[Phone] = x._2.map(_._2.get)
          Contact(x._1.firstName, x._1.lastName, phones.map(_.phoneNumber))
        } else {
          Contact(x._1.firstName, x._1.lastName, Seq())
        }
      }.toSeq
    }
  }
}

知道如何执行此查询吗？

推荐答案

而不是names joinLeft groups。执行groups join phones on (_.phoneId === id)，然后使用namesjoinLeft

val q = for {
  (n, pair) <- names joinLeft (groups join phones on (_.phoneId === _.id)) on (_.id === _._1.nameId)
} yield (n, pair)

这篇关于Scala/SLICK：三向联接不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！