Slick 3.0 many-to-many query with connection as iterable

Question

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

Between events and interests there is a many-to-many relationship. 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 the 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 request and receive tuples (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 have.

I want to be able to populate the case class as follows:

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

The problem is that if I update the case class this way, it will ruin my def * projection in the EventsTable . Also, I will have to rename the EventsTable.interests filter to something like EventsTable.interestIds , which is a little ugly, but I could live if necessary.

Also, I cannot find a way to write a for request that gives (event.name, Seq(interest.name)) . In any case, this is just a step for me, allowing me to get a tuple (EventDao, Seq(InterestDao)) , which 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 requests everything will be returned, but for others there will be only the first 3.

Answer 1

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

 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) 

Answer 2

The only problem with groupBy is losing order. You can reset the result. I wrote 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 

This may be due to some love. Now it takes a function A, B => Boolean, to determine if B A belongs to the function A, B => A, which adds B to A.

Virtualeyes also has a point. In Postgres, you can use the array_agg function to use a bit less bandwidth from db.