Filling date gaps in Anorm results

Question

i'm new to Scala, Play and Anorm, so I'm wondering how can I do this.

I have a query to my database, which returns a date, with a DD/MM HH:OO format, and a Long, which is a total.

I want to display a total per hour graph, so I create a byhour parser:

val byhour = {
get[Option[String]]("date") ~ get[Long]("total") map {
    case date ~ total => (date, total)
}

And this, of course, only returns the dates where I have data. I want to fill the date gaps with the date and a total of 0, but I'm not sure how to do it.

Thanks in advance!

edit: I know it's possible to do this in MySQL, but I'd prefer to do this in Scala itself to keep the queries clean.

possible duplicate of [generate days from date range](http://stackoverflow.com/questions/2157282/generate-days-from-date-range) — Sean Vieira, Nov 06 '14 at 20:26
the difference is I don't want to do this in the SQL query, but in Scala, so it's not the same question — J. Born, Nov 06 '14 at 20:39

score 0 · Answer 1 · edited Nov 07 '14 at 02:47

I don't think that related to Anorm directly, which there will allow you to fill gaps among parsed results afterward.

First option you get unordered result as List[(String, Long)] using .as(byhour.*), sort it by date and then fill with zero for missing date.

SQL"...".as(byhour.*).sortBy(_._1).
  foldLeft(List.empty[(String, Long)]) {
    case (p :: l, (d, t)) =>
      (d, t) :: prefill(p, d, l)
    case (l, (d, t)) =>
      (d, t) :: l // assert l == Nil
  }.reverse

/**
 * @param p Previous/last tuple
 * @param d Current/new date
 * @param l List except `p`
 * @return List based on `l` with `p` prepended and eventually before with some filler tuple prepended.
 */
def prefill(p: (String, Long), d: String, l: List[(String, Long)]): List[(String, Long)] = ???

Otherwise if you query returns results ordered by date you can use Anorm streaming API and fill gap as soon as it's discovered.

// Anorm 2.3
import anorm.Success

SQL"... ORDER BY date ASC".apply().
  foldLeft(List.empty[(String, Long)]) {
    case (l, row) =>
      byhour(row) match {
        case Success((d, t)) => 
          l match {
            case p :: ts =>
              (d, t) :: prefill(p, d, l)
            case _ => (d, t) :: l
          }
        case _ => ??? // parse error
      }
  }.reverse

Filling date gaps in Anorm results

1 Answers1