scalaslickdaoderived

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!