Slick 3.0 many-to-many query with the join as an iterable

Question

I've created a many-to-many collection using Slick 3.0, but I'm struggling to retrieve data in the way I want.

There is a many-to-many relationship between Events and Interests. Here are my tables:

case class EventDao(title: String,
                    id: Option[Int] = None)


class EventsTable(tag: Tag)
  extends Table[EventDao](tag, "events") {

  def id = column[Int]("event_id", O.PrimaryKey, O.AutoInc)
  def title = column[String]("title")

  def * = (
    title,
    id.?) <> (EventDao.tupled, EventDao.unapply)

  def interests = EventInterestQueries.query.filter(_.eventId === id)
    .flatMap(_.interestFk)
}


object EventQueries {

  lazy val query = TableQuery[EventsTable]

  val findById = Compiled { k: Rep[Int] =>
    query.filter(_.id === k)
  }
}

Here's EventsInterests:

case class EventInterestDao(event: Int, interest: Int)


class EventsInterestsTable(tag: Tag)
  extends Table[EventInterestDao](tag, "events_interests") {

  def eventId = column[Int]("event_id")
  def interestId = column[Int]("interest_id")

  def * = (
    eventId,
    interestId) <> (EventInterestDao.tupled, EventInterestDao.unapply)

  def eventFk = foreignKey("event_fk", eventId, EventQueries.query)(e => e.id)
  def interestFk = foreignKey("interest_fk", interestId, InterestQueries.query)(i => i.id)
}


object EventInterestQueries {
  lazy val query = TableQuery[EventsInterestsTable]
}

And finally Interests:

case class InterestDao(name: String,
                       id: Option[Int] = None)

class InterestsTable(tag: Tag)
  extends Table[InterestDao](tag, "interests") {

  def id = column[Int]("interest_id", O.PrimaryKey, O.AutoInc)
  def name = column[String]("name")
  def name_idx = index("idx_name", name, unique = true)

  def * = (
    name,
    id.?) <> (InterestDao.tupled, InterestDao.unapply)

  def events = EventInterestQueries.query.filter(_.interestId === id)
    .flatMap(_.eventFk)
}


object InterestQueries {

  lazy val query = TableQuery[InterestsTable]

  val findById = Compiled { k: Rep[Int] =>
    query.filter(_.id === k)
  }
}

I can query and retrieve tuples of (event.name, interest) with the following:

val eventInterestQuery = for {
  event <- EventQueries.query
  interest <- event.interests
} yield (event.title, interest.name)

Await.result(db.run(eventInterestQuery.result).map(println), Duration.Inf)

So this is what I currently have.

What I want is to be able to populate a case class like:

case class EventDao(title: String,
                interests: Seq[InterestDao],
                id: Option[Int] = None)

The trouble is that if I update my case class like this, it messes up my def * projection in EventsTable. Also, I'll have to rename the EventsTable.interests filter to something like EventsTable.interestIds which is a bit ugly but I could live with if necessary.

Also, I can't find a way of writing a for query that yields (event.name, Seq(interest.name)). Anyway, that's just a stepping stone to me being able to yield a (EventDao, Seq(InterestDao)) tuple which is what I really want to return.

Does anyone know how I can achieve these things? I also want to be able to 'take' a certain number of Interests, so for some queries all would be returned, but for others only the first 3 would be.

score 7 · Accepted Answer · answered Mar 28 '15 at 23:10

7

So after reading this page and chatting on the mailing list, I finally got it working:

val eventInterestQuery = for {
  event <- EventQueries.query
  interest <- event.interests
} yield (event, interest)

Await.result(db.run(eventInterestQuery.result
  // convert the interests to a sequence.
  .map {
  _.groupBy(_._1)
    .map {
      case (k,v) => (k, v.map(_._2))
  }.toSeq
}
), Duration.Inf)

answered Mar 28 '15 at 23:10

jbrown

7,518
16
69
117

8

That's insane, you're forced to send the *entire ungrouped result set* over the wire *and then* group on the client. For small result sets, sure, no big deal, but sending 100K+ records that could easily be grouped at the database level, wow. – virtualeyes Apr 19 '15 at 10:30
1

@virtualeyes: what approach would you recommend then? – LuGo May 29 '15 at 10:09
1

@virtualeyes I haven't read the complete question but can't the groupBy be used directly in the query? Like described in the documentation: http://slick.typesafe.com/doc/3.0.0/queries.html#aggregation – User Jul 08 '15 at 17:30
1

@Ixx OP wants to groupBy yielding a nested collection (i.e. `Seq(interest.name)`) which Slick does not yet support. Basically any groupBy result set must be flattened via aggregate operation (like min, max, sum, etc.). – virtualeyes Jul 08 '15 at 23:14
Using Await is a bad practise! – Felix Sep 08 '17 at 09:50
@virtualeyes , can you please tell me how to achieve this at database level if we have more than one join query. – thedevd Aug 26 '20 at 17:01

score 2 · Answer 2 · answered May 12 '15 at 18:03

The only issue with groupBy is you lose order. You could fold the result. I've written this helper for my current project:

def foldOneToMany[A, B](in: Seq[(A, Option[B])], eq: (A, B) => Boolean)
                        (f: (A, B) => A): Seq[A] = 
  in.foldLeft(List.empty[A]) {
    case (head :: tail, (_, Some(rel))) if eq(head, rel) =>
      f(head, rel) :: tail
    case (r, (el, Some(rel))) => f(el, rel) :: r
    case (r, (el, None)) => el :: r
  }.reverse

It could do with some love. Now it takes in a function A,B => Boolean to determine if B belongs to A and a function A,B => A that adds B to A.

Virtualeyes also has a point. In Postgres you could use array_agg function to use a little less bandwidth from the db.

Slick 3.0 many-to-many query with the join as an iterable

2 Answers2

Linked