Jared Jared -4 years ago 93
Scala Question

Functional way to go from a Slick join to entities with parent-child relationships?

I'm giving Slick 3.2 a try for the data layer in a new Scala Play app, and I'm spinning my wheels a little trying to get from a query against a few related tables to the case class representation of those entities. The application sends data to arbitrary destinations either instantly (whenever data is updated in our system) or on some schedule. That is represented by the following three Postgres tables:

CREATE TABLE destinations (
destination_id SERIAL PRIMARY KEY,
endpoint VARCHAR(100) NOT NULL,
protocol VARCHAR(100) NOT NULL

CREATE TABLE export_triggers (
export_trigger_id SERIAL PRIMARY KEY,
destination_id INTEGER NOT NULL REFERENCES destinations,
trigger_type VARCHAR(50) NOT NULL,
export_schedule_id INTEGER REFERENCES export_schedules

CREATE TABLE export_schedules (
export_schedule_id SERIAL PRIMARY KEY,
description VARCHAR(1000),

The following case classes represent the entities in Scala (Protocol and TriggerType are enums which I've left off for brevity):

case class Destination(endpoint: String, protocol: Protocol, exportTriggers: Seq[ExportTrigger])
case class ExportTrigger(triggerType: TriggerType, schedule: Option[ExportSchedule])
case class ExportSchedule(description: Option[String], cron: String)

There are also case classes generated by Slick Codegen that represent a row in each table. They are
, and

A destination can contain many ExportTriggers, and an ExportTrigger may contain 0 or 1 ExportSchedules. I'm trying to implement a function
getDestinations(jobId: Int)
which takes a jobId and retrieves all destinations (including triggers and schedules) for that job. Here's a Slick query that should get all the data (new lines added for readability):

ExportTriggers.filter(_.jobId === id)
join Destinations on (_.destinationId === _.destinationId)
joinLeft ExportSchedules on (_._1.exportScheduleId === _.exportScheduleId)

The inner type of this expression is
Seq[((ExportTriggersRow, DestinationsRow), Option[ExportSchedulesRow])]
, so it should contain the mappings I need to create the model I'm looking for, but I'm having a hard time going from that flat sequence of rows to the
structure in a functional way without going down some seriously unreadable rabbit holes. Any ideas?

Answer Source

With the information you provided, I would go with something like this:

val result: Seq[((ExportTriggersRow, DestinationsRow), Option[ExportSchedulesRow])]

val grouped: Seq[Destination] = result.groupBy {
    case ((_, destinationRow), _) => destinationRow
} map {
    case (destinationRow, resultList) =>
        val triggers = resultList.map {
          case ((triggerRow, _), optScheduleRow) =>
             val schedule = optScheduleRow.map { row => 
                ExportSchedule(row.description, row.cron)

             ExportTrigger(TriggerType(triggerRow.triggerType), schedule)
        Destination(destinationRow.endpoint, Protocol(destinationRow.protocol), triggers)

First group by the destinationRow, then convert the triggers and the optional schedule and finally create the Destination.

EDIT Corrected the result type of grouped. It's of course only a sequence of Destinations, containing their ExportTriggers, which in turn might contain an ExportSchedule

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download