0

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?

Daniel Alexandrov
  • 1,299
  • 8
  • 13
  • Did you try flatMap instead of the map? I'm not sure about this but just a guess – joesan Dec 18 '17 at 08:57
  • I think so, but I don't remember what the result is, I am fighting with this query for quite a long, I will be back to it again later today and let you know. – Daniel Alexandrov Dec 18 '17 at 10:59

1 Answers1

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

SergGr
  • 23,570
  • 2
  • 30
  • 51
  • Yes, it is a valid PostgreSQL query, but I am not sure if it is translated correctly to Quill's AST. I have 20 fields in the Book table. I will try to join Book again and let you know. – Daniel Alexandrov Dec 18 '17 at 10:57
  • @DanielAlexandrov, OK, I was wrong, this might be a valid SQL query **_if_** the DB supports _optional_ Feature T301 "Functional Dependencies" but it looks like Quill doesn't provide a way to express such feature in its DSL so you still have to use one of non-optional ways: group by all fields or additional join. – SergGr Dec 18 '17 at 18:26
  • Your suggestion for grouping on more than 1 field from Book was quite useful, apparently it possible to add the table name itself in the GROUP BY. – Daniel Alexandrov Dec 18 '17 at 19:51