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

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!

Solution

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!