Scala/Slick: 3 way join is not working

问题:

I have 2 tables (Names and Phones) and another table Groups effectively linking the two (using a foreign key towards Names and Phones). I am trying to query names and phone numbers while some names need not have phone numbers.

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 "value phoneId is not a member of (Contacts.NameTable, slick.lifted.Rep[Option[Contacts.GroupTable]])"

Here is the complete source code:

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

Any idea how to do this query ?

回答1:

Instead of doing names joinLeft groups. Do groups join phones on (_.phoneId === id) and then joinLeft with names

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

回答2:

If you want a clean approach to do this, you can try also

val query = names joinLeft groups on {
    case (n, g) => n.id === g.nameId
} join phones on {
    case ((n, g), p) => n.phoneId === p.id
} map {
    case ((n, g), p) => (n, p)
}

val result = db.run(query.result)

This will be much better / clean way if you have multiple tables to join. :)