Slick also provides a groupBy
method that behaves like the
groupBy
method of native Scala collections. Let's get a list of candidates with all the donations for each candidate:
scala> val grouped = Tables.transactions.groupBy { _.candidate } grouped: scala.slick.lifted.Query[(scala.slick.lifted.Column[... scala> val aggregated = grouped.map { case (candidate, group) => (candidate -> group.map { _.amount }.sum) } aggregated: scala.slick.lifted.Query[(scala.slick.lifted.Column[... scala> val groupedDonations = db.withSession { implicit session => aggregated.list } groupedDonations: List[(String, Option[Long])] = List((Bachmann, Michele,Some(7439272)),...
Let's break this down. The first statement, transactions.groupBy { _.candidate }
, specifies the key by which to group. You can think of this as building an intermediate list of (String, List[Transaction])
tuples mapping the group key to a list of all the table rows that satisfy this key. This behavior is identical to calling groupBy
on a Scala collection.
The call to groupBy
must be followed by a map
that aggregates the groups. The function passed to map
must take the tuple (String, List[Transaction])
pair created by the groupBy
call as its sole argument. The map
call is responsible for aggregating the List[Transaction]
object. We choose to first pick out the amount
field of each transaction, and then to run a sum over these. Finally, we call .list
on the whole pipeline to actually run the query. This just returns a Scala list. Let's convert the total donations from cents to dollars:
scala> val groupedDonationDollars = groupedDonations.map { case (candidate, donationCentsOption) => candidate -> (donationCentsOption.getOrElse(0L) / 100) } groupedDonationDollars: List[(String, Long)] = List((Bachmann, Michele,74392),... scala> groupedDonationDollars.sortBy { _._2 }.reverse.foreach { println } (Romney, Mitt,20248496) (Obama, Barack,8496347) (Paul, Ron,565060) (Santorum, Rick,334926) (Perry, Rick,301780) (Gingrich, Newt,277079) (Cain, Herman,210768) (Johnson, Gary Earl,83610) (Bachmann, Michele,74392) (Pawlenty, Timothy,42500) (Huntsman, Jon,23571) (Roemer, Charles E. 'Buddy' III,8579) (Stein, Jill,5270) (McCotter, Thaddeus G,3210)