Slick and nesting case classes a no go?

Question

I am using slick (and the play framework) to build an application on top of an existing database. I cannot change the database structure.

My database has the following 2 tables:

Meeting:

• id (PK)
• name
• chairman_id (FK to Person.id)
• houseman_id (FK to Person.id)

Person:

• id (pk)
• first_name
• last_name

I wanted to define my case classes like this:

case class Meeting (
  id: Int,
  name: String,
  chairman: Person,
  houseman: Person
) 

case class Person (
   id: Int,
   firstName: String,
   lastName: String
)

But from the very minimal slick documentation around this, it looks like I have to keep the ids in the case class rather than using "Person". Is that correct?

Whats the best approach for this? Sorry for the relatively open question, very new to scala, slick and play.

Thanks,

Ed

Answer 1

You have foreign keys, they don't translate to case classes, they translate to ids:

case class Meeting (
  id: Int,
  name: String,
  chairmanId: Int,
  housemanId: Int) 

 case class Person (
  id: Int,
  firstName: String,
  lastName: String)

And the schema would be something like:

 case class Meeting (
                   id: Int,
                   name: String,
                   chairmanId: Int,
                   housemanId: Int)

 case class Person (
                  id: Int,
                  firstName: String,
                  lastName: String)


class Meetings(tag: Tag) extends Table[Meeting](tag, "meeting") {
  def * = (id, name, chairmanId, housemanId) <>(Meeting.tupled, Meeting.unapply)

  def ? = (id.?, name, chairmanId, housemanId).shaped.<>({
    r => import r._
      _1.map(_ => Meeting.tupled((_1.get, _2, _3, _4)))
  }, (_: Any) => throw new Exception("Inserting into ? projection not supported."))

  val id: Column[Int] = column[Int]("id", O.AutoInc, O.PrimaryKey)
  val name: Column[String] = column[String]("name")
  val chairmanId: Column[Int] = column[Int]("chairmanId")
  val housemanId: Column[Int] = column[Int]("housemanId")

  lazy val meetingChairmanFk =
    foreignKey("meeting_chairman_fk", chairmanId, persons)(r => r.id, onUpdate = ForeignKeyAction.Restrict, onDelete = ForeignKeyAction.Cascade)


  lazy val meetingHousemanFk =
    foreignKey("meeting_houseman_fk", housemanId, persons)(r => r.id, onUpdate = ForeignKeyAction.Restrict, onDelete = ForeignKeyAction.Cascade)


}

lazy val meetings = new TableQuery(tag => new Meetings(tag))

class Persons(tag: Tag) extends Table[Person](tag, "person") {
  def * = (id, firstName, lastName) <>(Person.tupled, Person.unapply)

  def ? = (id.?, firstName, lastName).shaped.<>({
    r => import r._
      _1.map(_ => Person.tupled((_1.get, _2, _3)))
  }, (_: Any) => throw new Exception("Inserting into ? projection not supported."))

  val id: Column[Int] = column[Int]("id", O.AutoInc, O.PrimaryKey)
  val firstName: Column[String] = column[String]("firstname")
  val lastName: Column[String] = column[String]("lastname")


}

lazy val persons = new TableQuery(tag => new Persons(tag))

And it could be used like this:

val thisMeeting = meetings.filter(_.name === "thisMeeting").join(persons).on((m, p) => m.housemanId === p.id || m.chairmanId === p.id).list()

Or using for comprehension (which I personally find more legible):

val thatMeething = (for {
  m <- meetings
  p <- persons if (p.id === m.chairmanId || p.id === m.housemanId) && m.name === "thatMeeting"
} yield m.id).run

Note that the second query corresponds to an implicit inner join, other types of join are also supported, you can find them here.