Group by in many-to-many join with Quill

Question

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?

Answer 1

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.* while group 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 wrong

One way to fix it is to do group by by all fields. Assuming Book is declared as:

case class Book(id: Int, name: String)

you can do

  val qq: db.Quoted[db.Query[((Index, String), Seq[String])]] = quote {
    books
      .join(authorsBooks).on(_.id == _.book_id)
      .join(authors).on(_._2.author_id == _.id)
      .groupBy(r => (r._1._1.id, r._1._1.name))
      .map {
        case (bId, q) => {
          // (Book.tupled(bId), unquote(q.map(_._2.name).arrayAgg)) // doesn't work
          (bId, unquote(q.map(_._2.name).arrayAgg))
        }
      }
  }

  val res = db.run(qq).map(r => (Book.tupled(r._1), r._2))

Unfortunately it seems that you can't apply Book.tupled inside quote because you get error

The monad composition can't be expressed using applicative joins

but you can easily do it after db.run call to get back your Book.

Another option is to do group by just Book.id and then join the Book table again to get all the fields back. This might be actually cleaner and faster if there are many fields inside Book