mysql - Slick query with multiple joins, group by and having -

mysql - Slick query with multiple joins, group by and having -

i'm new scala , slick. i'm trying understand way should create queries slick. i've got far have been able create simple queries struggle combinding selects, joins, grouping bys etc.

i'm in middle of converting virtual bookshelf (meade php) scala, play , slick.

this query want accomplish:

list authors (limit 5), whom have @ to the lowest degree 3 books in bookshelf.

select a.id, a.firstname, a.lastname, count(b.id) amount book b left bring together book_author ba on b.id = ba.book_id left bring together author on a.id = ba.author_id grouping a.id having amount >= 3 order amount desc limit 5

apparently next code have managed create required joins:

(for(b <- books; <- authors; ba <- bookauthors; if b.id === ba.bookid && a.id === ba.authorid) yield (a.id, b.id)).run

i'm lost on how apply select, groupby , having code above.

just in case looking (derived 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 author case (author, bookid) => author }).map({ //count bookids case (author, authorbookids) => (author, authorbookids.map(_._2).count) //having count bookids >= 3 }).filter(_._2 >= 3) // order count desc .sortby(_._2.desc) // limit 5 .take(5)

mysql scala playframework slick