srvy srvy - 1 month ago 15
Scala Question

Scala/Slick: how to query tables and return sequence of custom objects

I am fairly new to scala and slick. I am not able to get the sequence of contacts from this function. Each Contact object will have first name, last name and a sequence of phone numbers. Name and phone numbers are stored in respective tables using a foreign key.

def testContacts: Future[Seq[Contact]] = {
names.result.map { namelist =>
var contacts = Seq[Contact]()
for (n <- namelist) {
println("NAME: " + n) // THIS IS PRINTED!
val phoneNumbers = for {
p <- phones.filter(_.nameId === n.id)
} yield p.phoneNumber
phoneNumbers.result.map { plist =>
contacts = contacts :+ Contact(n.firstName, n.lastName, plist)
// Q: HOW DO I RETURN "contacts" to the caller?
println("CONTACTS: " + plist) // THIS IS **NOT** PRINTED!
}
}
}
Future { Seq[Contact]() } // dummy statement to avoid compilation error
}


From console:

scala> Await.result(db.run(names.result), 1000 milliseconds)
res9: Seq[Example.NameTable#TableElementType] = Vector(Name(George,W,1), Name(John,A,2))

scala> Await.result(db.run(phones.result), 1000 milliseconds)
res10: Seq[Example.PhoneTable#TableElementType] = Vector(Phone(+1 301 531 1121,1,1), Phone(+1 301 748 5192,1,2), Phone(+1 301 531 4519,2,3), Phone(+1 202 667 9612,2,4), Phone(+1 202 667 4044,2,5))

scala> Await.result(testContacts, 1000 milliseconds)
res5: Seq[Contacts.Contact] = List()

scala>

Any help is appreciated. 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("dbconfig")

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

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

names.result.map { namelist =>
var contacts = Seq[Contact]()
for (n <- namelist) {
println("NAME: " + n)
val phoneNumbers = for {
p <- phones.filter(_.nameId === n.id)
} yield p.phoneNumber
phoneNumbers.result.map { plist =>
contacts = contacts :+ Contact(n.firstName, n.lastName, plist)
// Q: HOW DO I RETURN "contacts" to the caller?
println("CONTACTS: " + plist) // THIS IS NOT PRINTED!
}
}
}
Future { Seq[Contact]() } // dummy statement to avoid compilation error
}

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 Phone(phoneNumber: String, nameId: Long, 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")
def nameId = column[Long]("NAME")

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

def name_fk = foreignKey("Phones_Names_ID_FK", nameId, names)(_.id, onUpdate = ForeignKeyAction.Cascade, onDelete = ForeignKeyAction.Cascade)
}

lazy val phones = TableQuery[PhoneTable]

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

def testPhones = Seq(
Phone("+1 301 531 1121", 1L),
Phone("+1 301 748 5192", 1L),
Phone("+1 301 531 4519", 2L),
Phone("+1 202 667 9612", 2L),
Phone("+1 202 667 4044", 2L))

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
nameCount <- names ++= testNames
phoneCount <- phones ++= testPhones
} yield nameCount
}
}

Answer

Your testContacts query isn't very straight forward, I would use a join or a filter in a query statement and then unroll the row

Here is what I would try

def testContacts: Future[Seq[Contact]] = {
  val query = for {
    (n, pl) <- names join phones on (_.id === _.nameId)
  } yield (n, pl)
  db.run(query.result).map { (row) =>
    row.groupBy(_._1).map { r =>
      val name = r._1
      val phones = r._2.map(_._2.phoneNumber)
      Contact(name.firstName, name.lastName, phones)
    }.toSeq
  }
}

This uses the slick forcomp join syntax and then maps over the results

Comments