Slick query with multiple joins, group by and having

Question

I'm new to Scala and Slick. I'm trying to understand the way I should create queries with Slick. I've got so far that I have been able to create simple queries but struggle with combinding SELECTs, JOINs, GROUP BYs 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 accomplish:

List those authors (limit to 5), from whom I have at least 3 books in my 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 have managed to create the required joins:

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

I'm lost on how to apply SELECT, GROUPBY and HAVING to the code above.

Answer 1

Just in case someone is looking for it (derived 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)