Calculate a derived column in the select output - Scala Slick 3.2.3

Question

I am trying to write some REST API to fetch the data using Scala Slick 3.2.3. Is there a way to calculate a derived column and include it in the returned output?

My model:

case class Task(id: Option[TaskId], title: String, dueOn: String, status: String, createdAt: String, updatedAt: String)

Table class:

class TasksTable(tag: Tag) extends Table[Task](tag, _tableName = "TASKS") {
  def id: Rep[TaskId] = column[TaskId]("ID", O.PrimaryKey, O.AutoInc)
  def title: Rep[String] = column[String]("TITLE")
  def dueOn: Rep[String] = column[String]("DUE_ON")
  def status: Rep[String] = column[String]("STATUS")
  def createdAt: Rep[String] = column[String]("CREATED_AT")
  def updatedAt: Rep[String] = column[String]("UPDATED_AT")
  def * = (id.?, title, dueOn, status, createdAt, updatedAt) <> ((Task.apply _).tupled, Task.unapply)
}

DAO:

object TasksDao extends BaseDao {
  def findAll: Future[Seq[Task]] = tasksTable.result
}

I want to add a column in the response json called timeline with values "overdue", "today", "tomorrow", "upcoming", etc. calculated based on the dueOn value.

I tried searching but could not find any help. Any help with an example or any pointers would be highly appreciated. Thanks!

Answer 1

First I'd start from defining enum model for timeline:

object Timelines extends Enumeration {
  type Timeline = Value
  val Overdue: Timeline = Value("overdue")
  val Today: Timeline = Value("today")
  val Tomorrow: Timeline = Value("tomorrow")
  val Upcoming: Timeline = Value("upcoming")
}

Then I'd modify dueOne column type from plain String to LocalDate - this will be easier to do on DAO level, so Slick will handle parsing errors for us. So, to need to define custom type for LocalDate (see for more details: http://scala-slick.org/doc/3.0.0/userdefined.html#using-custom-scalar-types-in-queries).

// Define mapping between String and LocalDate
private val defaultDateFormat: DateTimeFormatter = DateTimeFormatter.ISO_DATE // replace it with formatter you use for a date

def stringDateColumnType(format: DateTimeFormatter): BaseColumnType[LocalDate] = {
 MappedColumnType.base[LocalDate, String](_.format(format), LocalDate.parse(_, format))
}

implicit val defaultStringDateColumnType: BaseColumnType[LocalDate] = stringDateColumnType(defaultDateFormat)

private val defaultDateFormat: DateTimeFormatter = DateTimeFormatter.ISO_DATE // replace it with formatter you use for a date

// Change `dueOn` from String to LocalDate
case class Task(id: Option[TaskId], title: String, dueOn: LocalDate, status: String, createdAt: String, updatedAt: String)

class TasksTable(tag: Tag) extends Table[Task](tag, _tableName = "TASKS") {
  def id: Rep[TaskId] = column[TaskId]("ID", O.PrimaryKey, O.AutoInc)
  def title: Rep[String] = column[String]("TITLE")
  def dueOn: Rep[LocalDate] = column[LocalDate]("DUE_ON") // Then replace column type
  def status: Rep[String] = column[String]("STATUS")
  def createdAt: Rep[String] = column[String]("CREATED_AT")
  def updatedAt: Rep[String] = column[String]("UPDATED_AT")
  def * = (id.?, title, dueOn, status, createdAt, updatedAt) <> ((Task.apply _).tupled, Task.unapply)
}

Then define API level model TaskResponse with new additional timeline field:

case class TaskResponse(id: Option[TaskId], title: String, dueOn: LocalDate, status: String, createdAt: String, updatedAt: String, timeline: Timeline)

  object TaskResponse {
    import Timelines._
    def fromTask(task: Task): TaskResponse = {
      val timeline = dueOnToTimeline(task.dueOn)
     TaskResponse(task.id, task.title, task.dueOn, task.status, task.createdAt, task.updatedAt, timeline)
    }

    def dueOnToTimeline(dueOn: LocalDate): Timeline = {
      val today = LocalDate.now()
      Period.between(today, dueOn).getDays match {
        case days if days < 0 => Overdue
        case 0 => Today
        case 1 => Tomorrow
        case _ => Upcoming
      }
    }
  }

Then you can create TasksService responsible for business logic of converting:

  class TasksService(dao: TasksDao)(implicit ec: ExecutionContext) {
    def findAll: Future[Seq[TaskResponse]] = {
      dao.findAll.map(_.map(TaskResponse.fromTask))
    }
  }

Hope this helps!