Summarizing/aggregating a Scala Slick object into

Question

I'm essentially trying to recreate the following SQL query using Scala Slick:

select labelOne, labelTwo, sum(countA), sum(countB) from things where date > 'blah' group by labelOne, labelTwo;

As you can see, it takes what a table of labeled things and aggregates them, summing various counts. A table with the following info:

ID | date | labelOne | labelTwo | countA | countB
-------------------------------------------------
0  | 0    | foo      | cheese   | 1      | 2
1  | 0    | bar      | wine     | 0      | 3
2  | 1    | foo      | cheese   | 3      | 4
3  | 1    | bar      | wine     | 2      | 1
4  | 2    | foo      | beer     | 1      | 1

Should yield the following result if queried across all dates:

labelOne | labelTwo | countA | countB
-------------------------------------
foo      | cheese   | 4      | 6
bar      | wine     | 2      | 4
foo      | beer     | 1      | 1

This is what my Scala code looks like:

import scala.slick.driver.MySQLDriver.simple._
import scala.slick.jdbc.StaticQuery
import StaticQuery.interpolation
import org.joda.time.LocalDate
import com.github.tototoshi.slick.JodaSupport._

case class Thing(
  id: Option[Long],
  date: LocalDate,
  labelOne: String,
  labelTwo: String,
  countA: Long,
  countB: Long)

// summarized version of "Thing": note there's no date in this object
// each distinct grouping of Thing.labelOne + Thing.labelTwo should become a "SummarizedThing", with summed counts
case class SummarizedThing(
  labelOne: String,
  labelTwo: String,
  countASum: Long,
  countBSum: Long)

trait ThingsComponent {
  val Things: Things

  class Things extends Table[Thing]("things") {
    def id       = column[Long]("id", O.PrimaryKey, O.AutoInc)
    def date     = column[LocalDate]("date", O.NotNull)
    def labelOne = column[String]("labelOne", O.NotNull)
    def labelTwo = column[String]("labelTwo", O.NotNull)
    def countA   = column[Long]("countA", O.NotNull)
    def countB   = column[Long]("countB", O.NotNull)

    def * = id.? ~ date ~ labelOne ~ labelTwo ~ countA ~ countB <> (Thing.apply _, Thing.unapply _)

    val byId = createFinderBy(_.id)
  }
}

object Things extends DAO {
  def insert(thing: Thing)(implicit s: Session) { Things.insert(thing) }

  def findById(id: Long)(implicit s: Session): Option[Thing] = Things.byId(id).firstOption

  // ???
  def summarizeSince(date: LocalDate)(implicit s: Session): Set[SummarizedThing] = {
    Query(Things).where(_.date > date).groupBy(x => (x.labelOne, x.labelTwo)).map {
      case(thing: Thing) => {
        // obviously this line below is wrong, but you can get an idea of what I'm trying to accomplish:
        // create a new SummarizedThing for each unique labelOne + labelTwo combo, summing the count columns
        new SummarizedThing(thing.labelOne, thing.labelTwo, thing.countA.sum, thing.countB.sum)
      }
    } // presumably need to run the query and map to SummarizedThing here, perhaps?
  }
}

The summarizeSince function is where I'm having trouble. I seem to be able to query Things just fine, filtering by date, and grouping by my fields... however, I'm having trouble summing countA and countB. With the summed results, I'd then like to create a SummarizedThing for each unique labelOne + labelTwo combination. Hopefully that makes sense. Any help would be greatly appreciated.

Answer 1

Same as the other answer, but expressed as a for comprehension, except that .get is exceptional so you probably need getOrElse.

val q = for { 
  ((l1,l2), ts) <- Things.where(_.date > date).groupBy(t => (t.labelOne, t.labelTwo)) 
} yield (l1, l2, ts.map(_.countA).sum.getOrElse(0L), ts.map(_.countB).sum.getOrElse(0L))

// see the SQL that generates.
println( q.selectStatement )
// select x2.`labelOne`, x2.`labelTwo`, sum(x2.`countA`), sum(x2.`countB`) 
// from `things` x2 where x2.`date` > '2013' group by x2.`labelOne`, x2.`labelTwo`

// map the result(s) of your query to your case class
q.map(SummarizedThing.tupled).list

Answer 2

presumably need to run the query and map to SummarizedThing here, perhaps?

Exactly.

Query(Things).filter(_.date > date).groupBy(x => (x.labelOne, x.labelTwo)).map {
  // match on (key,group) 
  case ((labelOne, labelTwo), things) => {
    // prepare results as tuple (note .sum returns an Option)
    (labelOne, labelTwo, things.map(_.countA).sum.get, things.map(_.countB).sum.get)
  }
}.run.map(SummarizedThing.tupled) // run and map tuple into case class