Multiple join slip request, group and

Question

I am new to Scala and Slick. I am trying to figure out how I should create queries using Slick. I still have that I could create simple queries, but struggle with combining SELECT, JOINs, GROUP BY, etc.

I'm in the middle of converting my virtual bookshelf (meade with PHP) to Scala, Play and Slick.

This is the query I want to execute:

List these authors (limit to 5), from which I have at least 3 books in the bookshelf.

SELECT a.id, a.firstname, a.lastname, count(b.id) AS amount FROM book b LEFT JOIN book_author ba ON b.id = ba.book_id LEFT JOIN author a ON a.id = ba.author_id GROUP BY a.id HAVING amount >= 3 ORDER BY amount DESC LIMIT 5 

Apparently, with the following code, I managed to create the necessary connections:

(for(b <- books; a <- authors; ba <- bookAuthors; if b.id === ba.bookId && a.id === ba.authorId) yield (a.id, b.id)).run

I get lost in how to apply SELECT, GROUPBY and HAVING to the code above.

Answer 1

Just in case, someone is looking for him (obtained from slick docs)

 (for { //joins book <- books bookAuthor <- bookAuthors if book.id === bookAuthor.bookId author <- authors if bookAuthor.authorId === author.id } yield (author, book.id)).groupBy({ //group by author case (author, bookId) => author }).map({ //count bookIds case (author, authorBookIds) => (author, authorBookIds.map(_._2).count) //having count bookIds >= 3 }).filter(_._2 >= 3) // order by count desc .sortBy(_._2.desc) // limit 5 .take(5)