20

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?

Community
  • 1
  • 1
Roman
  • 5,651
  • 1
  • 30
  • 41

2 Answers2

8

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
panther
  • 767
  • 5
  • 21
  • As Sky mentioned earlier in a comment, there is no way to do this in slick atm. I guess the `.groupBy` method you proposed is the way to go for now. – Roman Jun 15 '15 at 12:13
  • @Roman Would this return an empty `Seq` if a library didn't have any books? If so, it might not quite match expectations. – acjay Mar 18 '16 at 17:28
  • @acjay: In that case this leads to an empty `Seq` indeed. If you want to have all libraries in your result no matter if they have books or not you can use outer joins instead of inner joins (`joinLeft`, `joinRight` in slick terms). Feel free to open a follow up question if this is not what you are looking for :) – Roman Mar 18 '16 at 17:39
  • @Roman I *think* I've figured out my case (which is similar to your question). I used `joinLeft`, then after I run the query, I'm doing `.groupBy(_._1).mapValues(_.flatMap(_._2)).toSeq` to get to `Seq[(OneTable, Seq[ManyTable])]`. – acjay Mar 18 '16 at 18:18
  • @acjay glad to hear. Thumbs up! – Roman Mar 18 '16 at 18:26
0

To what you want to map to, db.run returns a Future(of something), a Future[Seq[(Library, Seq[Book])]] in your case. When mapping over a future you have access to the Seq and you can transform it to something else to get a new Future.

Adi
  • 727
  • 3
  • 10
  • Thank you for your answer. Unfortunately this is not what I was looking for. I just updated my question and tried to be a little more specific. – Roman Jun 01 '15 at 12:24