15

I have a database that contain activities with a one-to-many registrations relation. The goal is to get all activities, with a list of their registrations.

By creating a cartesian product of activities with registrations, all necessary data to get that data is out is there. But I can't seem to find a nice way to get it into a scala collection properly; let's of type: Seq[(Activity, Seq[Registration])]

case class Registration(
  id: Option[Int],
  user: Int,
  activity: Int
)

case class Activity(
  id: Option[Int],
  what: String,
  when: DateTime,
  where: String,
  description: String,
  price: Double
)

Assuming the appropriate slick tables and tablequeries exist, I would write:

val acts_regs = (for {
   a <- Activities
   r <- Registrations if r.activityId === a.id
} yield (a, r))
  .groupBy(_._1.id)
  .map { case (actid, acts) => ??? }
}

But I cannot seem to make the appropriate mapping. What is the idiomatic way of doing this? I hope it's better than working with a raw cartesian product...

In Scala

In scala code it's easy enough, and would look something like this:

  val activities = db withSession { implicit sess =>
    (for {
      a <- Activities leftJoin Registrations on (_.id === _.activityId)
    } yield a).list
  }

  activities
    .groupBy(_._1.id)
    .map { case (id, set) => (set(0)._1, set.map(_._2)) }

But this seems rather inefficient due to the unnecessary instantiations of Activity which the table mapper will create for you. Neither does it look really elegant...

Getting a count of registrations

The in scala method is even worse when only interested in a count of registrations like so:

val result: Seq[Activity, Int] = ???

In Slick

My best attempt in slick would look like this:

  val activities = db withSession { implicit sess =>
    (for {
      a <- Activities leftJoin Registrations on (_.id === _.activityId)
    } yield a)
      .groupBy(_._1.id)
      .map { case (id, results) => (results.map(_._1), results.length) }
  }

But this results in an error that slick cannot map the given types in the "map"-line.

A.J.Rouvoet
  • 1,203
  • 1
  • 14
  • 29
  • The count version at least has a simple path [described in the docs](http://slick.typesafe.com/doc/2.1.0/sql-to-slick.html#group-by) – Sean Vieira Aug 28 '14 at 12:45
  • The difference with the docs is that I'm interested in the whole activity along with the aggregation over it's registrations. And I don't know how to map that. Can you help out? – A.J.Rouvoet Aug 28 '14 at 12:51
  • Just to be clear: I would like something like: `SELECT l.*, count(r.id) from activities l join registrations r where l.id = r.activityId`; and have it mapped to `Seq[(Activity, Int)]` – A.J.Rouvoet Aug 28 '14 at 12:59
  • Just read your last comment.. Not sure if this will work, but have you tried `groupBy(_._1)` which should then spit out the entire activity entity as the key. – atom.gregg Aug 28 '14 at 13:46
  • Also.. Your original question asked for the Activity and the list of related Registrations. I had a similar issue earlier and solved this requirement by making two calls to the database, one projected with `.head` and the other with `.list`. I understand this makes two 'calls' to the database, but because we've got an open session/connection the actual overhead is very minimal. If you don't mind expressing your problem into two calls (i.e. more code) then I think you can achieve your goal this way. – atom.gregg Aug 28 '14 at 13:50
  • Grouping by anything more than a single column fails for me with an SQLException. – A.J.Rouvoet Aug 28 '14 at 13:58
  • And about getting the list of registrations: I don't mind the two calls. I landed on something similar for getting the activities + counts now (list activities, list grouped registration counts for all activities). But it's not very 'elegant'. – A.J.Rouvoet Aug 28 '14 at 14:01

2 Answers2

6

I would suggest:

  val activities = db withSession { implicit sess =>
    (for {
      a <- Activities leftJoin Registrations on (_.id === _.activityId)
    } yield a)
      .groupBy(_._1)
      .map { case (activity, results) => (activity, results.length) }
  }

The problem with 

  val activities = db withSession { implicit sess =>
    (for {
      a <- Activities leftJoin Registrations on (_.id === _.activityId)
    } yield a)
      .groupBy(_._1.id)
      .map { case (id, results) => (results.map(_._1), results.length) }
  }

is that you can't produce nested results in group by. results.map(_._1) is a collection of items. SQL does implicit conversions from collections to single rows in some cases, but Slick being type-safe doesn't. What you would like to do in Slick is something like results.map(_._1).head, but that is currently not supported. The closest you could get is something like (results.map(_.id).max, results.map(_.what).max, ...), which is pretty tedious. So grouping by the whole activities row is probably the most feasible workaround right now.

cvogt
  • 11,260
  • 30
  • 46
  • I really thought I tried this. But it does seem to work now. I thought the groupBy on multiple columns was giving me problems because I got an SQLException saying it expected only one operand. But I suppose that was for something else then. – A.J.Rouvoet Aug 28 '14 at 22:13
  • I would accept this answer (also thanks for the insight as to the "why" it doesn't work) except that it only works for the case where you are interested in an aggregate. If groupBy does not work if I'm interested in the whole Seq of Registrations, what would you then suggest? – A.J.Rouvoet Aug 28 '14 at 22:15
  • Btw: I suspect that the 'implicit conversion' that SQL does, is exactly why this case is deceivingly nasty. The SQL made it look so simple... But I would suggest that something on the lines that you explain here should be added to the docs; the docs cover a typical case, but I had to experiment a lot to find out what the boundaries were of what is possible; and then my conclusion was wrong because of an error elsewhere. – A.J.Rouvoet Aug 28 '14 at 22:22
  • 1
    SQL's and Slick's groupBy don't allow you to return nested collections. If you need that you need to make an outer join or two separate queries instead and bring them into the desired shape on the client side using plain old scala collections. – cvogt Aug 28 '14 at 22:48
  • Thanks for the complete answer. My answer than covers one possible way to achieve getting the complete list of registrations per activity in 2 queries. – A.J.Rouvoet Aug 29 '14 at 09:36
1

A solution for getting all registrations per activity:

    // list of all activities
    val activities = Activities
    // map of registrations belonging to those activities
    val registrations = Registrations
      .filter(_.activityId in activities.map(_.id))
      .list
      .groupBy(_.activityId)
      .map { case (aid, group) => (aid, group.map(_._2)) }
      .toMap

    // combine them
    activities
      .list
      .map { a => (a, registrations.getOrElse(a.id.get, List()))

Which gets the job done in 2 queries. It should be doable to abstract this type of "grouping" function into a scala function.

A.J.Rouvoet
  • 1,203
  • 1
  • 14
  • 29