I am trying to achieve with Quill what the following PostgreSQL query does:
select books.*, array_agg(authors.name) from books
join authors_books on(books.id = authors_books.book_id)
join authors on(authors.id = authors_books.author_id)
group by books.id
For now I have this in my Quill version:
val books = quote(querySchema[Book]("books"))
val authorsBooks = quote(querySchema[AuthorBook]("authors_books"))
val authors = quote(querySchema[Author]("authors"))
val q: db.Quoted[db.Query[(db.Query[Book], Seq[String])]] = quote{
books
.join(authorsBooks).on(_.id == _.book_id)
.join(authors).on(_._2.author_id == _.id)
.groupBy(_._1._1.id)
.map {
case (bId, q) => {
(q.map(_._1._1), unquote(q.map(_._2.name).arrayAgg))
}
}
}
How can I get rid of the nested query in the result (db.Query[Book]) and get a Book instead?
I might be a little bit rusty with SQL but are you sure that your query is valid? Particularly I find suspicious that you do
select books.*
whilegroup by books.id
i.e. you directly return fields that you didn't group by. And attempt to translate that wrong query directly is what makes things go wrongOne way to fix it is to do
group by
by all fields. AssumingBook
is declared as:you can do
Unfortunately it seems that you can't apply
Book.tupled
insidequote
because you get errorbut you can easily do it after
db.run
call to get back yourBook
.Another option is to do group by just
Book.id
and then join theBook
table again to get all the fields back. This might be actually cleaner and faster if there are many fields insideBook