srvy srvy - 1 month ago 21
Scala Question

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("+1 301 531 1121", 1L),
Phone("+1 301 748 5192", 1L),
Phone("+1 202 531 4519", 2L),
Phone("+1 202 667 9612", 2L),
Phone("+1 202 667 4044", 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 ?

Answer

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)