Slick 3.0.0: How to query one-to-many / many-to-many relations

Question

Basically the same question has been asked about a year ago for slick 2.x (scala slick one-to-many collections). I'm wondering if there has any progression been made with the release of reactive slick.

Let's say for example we have three tables. library, book and library_to_book where a library has many books. What I want is a list of libraries with their books. In scala this would be something like Seq[(Library, Seq[Book])]. The query I have is as follows:

val q = (for {
  l   <- libraries
  ltb <- libraryToBooks if l.id === ltb.libraryId
  b   <- books if ltb.bookId === b.id
} yield (l, b)
db.run(q.result).map( result => ??? )

results in this case is of type Seq[(Library, Book)]. How do I have to change my query to get a result of type Seq[(Library, Seq[Book])] instead? What is the "slick way" of writing such queries?

panther panther · Accepted Answer · 2015-06-12T19:51:44

IMO your code looks fine. It really depends on what feels more readable to you. Alternatively, you can use join as well:

val findBooksQuery = libraries
  .join(libraryToBooks).on(_.id === _.libraryId)
  .join(books).on(_.id === _._2.bookId)
  .result

val action = (for {
  booksResult <- findBooksQuery
} yield {
  booksResult.map { row =>
    val (libraryTableRow, libraryToBooksTableRow) = row._1
    val booksTableRow = row._2
    // TODO: Access all data from the rows and construct desired DS
  }
}

db.run(action)

You can then do a groupBy on a particular key to get the kind of data structure you are looking for. In this case, it would be more evolved as it is join across three tables. Example, add following to your query:

val findBooksQuery = libraries
  .join(libraryToBooks).on(_.id === _.libraryId)
  .join(books).on(_.id === _._2.bookId)
  // To group by libraries.id
  .groupBy(_._1.id)
  .result

Slick 3.0.0: How to query one-to-many / many-to-many relations

2 Answers