Groupby on multiple objects generates invalid SQL in slick

Question

I am writing a query that calculates a possible score for a QuestionAnswer, when executing the query I get a PSQLException

Info about the model

A QuestionAnswer can have several (at least one) questionAnswerPossibilities, since there are multiple ways to answer the question correctly.

Every questionAnswerPossibility has several questionAnswerParts, in the query below we query the score per questionAnswerPossibility.

The Problematic Query

The query itself does generate SQL, but the SQL can not be executed

  def queryMogelijkePuntenByVragenViaOpenVragen()(implicit session: Session) = {
    (for{
      ovam <- OpenVraagAntwoordMogelijkheden //questionAnswerPossibilites
      ovad <- OpenVraagAntwoordOnderdelen if ovad.ovamId === ovam.id //questionAnswerParts
      ova <- OpenVraagAntwoorden if ovam.ovaId === ova.id //questionAnswers
    } yield ((ova, ovam), ovad.punten))
      .groupBy{ case ((ova, ovam), punten) => (ova, ovam)}
      .map{ case ((ova, ovam), query) => (ova, ovam, query.map(_._2).sum)}
}

Here the generated SQL (postgreSQL)

select x2."id", x2."vraag_id", x3."id", x3."volgorde", x3."ova_id", sum(x4."punten") 
from "open_vraag_antwoord_mogelijkheden" x3, "open_vraag_antwoord_onderdelen" x4, "open_vraag_antwoorden" x2 
where (x4."ovam_id" = x3."id") and (x3."ova_id" = x2."id")
group by (x2."id", x2."vraag_id"), (x3."id", x3."volgorde", x3."ova_id")

The problem is that the SQL can not execute , I get the following error

play.api.Application$$anon$1:
Execution exception[[
PSQLException: ERROR: column "x2.id" must appear in the GROUP BY clause or be used in an aggregate function
  Position: 8]]

The SQL that is genarated contains too many brackets, the last part of the SQL should be

group by x2."id", x2."vraag_id", x3."id", x3."volgorde", x3."ova_id"

However slick generates it with brackets, am I doing something wrong here? Or is this a bug?

A shorter, simplified code which can be reproduced would be handsome. It would mean more work for you, but only once - instead of everybody else having to set up the code for testing. — user unknown
btw.: vraagIds and session aren't used at all - what do they do in the method declaration? — user unknown
Ah you are right, the vraagIds were used at some point in this query, however, when I shortened the query to isolate the issue, I forgot to remove it (I will do so right now). I'll shorten the code this afternoon — Wellingr

Wellingr Wellingr · Accepted Answer · 2013-09-11T13:54:44

I solved the issue

...
    } yield ((ova.id, ovam.id), ovad.punten))

Because I now only yield the nessecary id's and not all data, the sql that is generated does not contain the unnecessary braces that caused the SQL error.

I really wanted more data than just those id's, but I can work around this by using this query as a subquery, the outer query will fetch all the needed data for me.

Groupby on multiple objects generates invalid SQL in slick

1 Answers