group by needs to be part of the subquery

[Colin Clausen]

Hey,

so I have this query I want to convert to slick:

SELECT Sum(diff)
FROM   (SELECT Date_part('epoch', age(Max(s.timepoint), Min(s.timepoint))) * 1000 AS
               diff
        FROM   v2_game g,
               v2_player_game_rel r,
               v2_timepoint_stats s
        WHERE  g.id = r.g
               AND NOT r.locked
               AND s.player_game = r.id
               AND r.p = ?
        GROUP  BY g.id) AS foo 

I have tried a few things, but I always either get a wrong query or a query that is straight out invalid.

My best guess looks like this:

Datapoints > "v2_timepoint_stats"

r.game > "r.g"

r.playerId > "r.p"

datePartLone and age or lifted functions from postgres

  val q = (for {
    s <- Datapoints
    r <- s.playerGame if (!r.locked && r.playerId === 410)
    g <- r.game
  } yield (g.id, datePartLong("epoch", age(s.time.max.get, s.time.min.get)) * 1000L)).groupBy(x => x._1).map {
    case (id, len) => len.map(_._2).sum
  }

This generates this SQL:

SELECT Sum(x2.x3)
FROM   (SELECT x4."id"
                      AS x5,
               Date_part('epoch', Age(Max(x6."timepoint"), Min(x6."timepoint")))
               * 1000
                      AS x3
        FROM   "v2_timepoint_stats" x6,
               "v2_player_game_rel" x7,
               "v2_game" x4
        WHERE  ( ( x7."id" = x6."player_game" )
                 AND ( ( NOT x7."locked" )
                       AND ( x7."p" = 410 ) ) )
               AND ( x4."id" = x7."g" )) x2
GROUP  BY x2.x5 

So this fails like with this message:
"column "x4.id" must appear in the GROUP BY clause or be used in an aggregate function"
because the inner query selects uses min and max on the date part, but no aggregate on the id (ofc since I want to group by on it). Moving the group by inside the subquery gives me the expected result.
I think my basic problem is that I cant find a way to do a group by that groups by a column that is not selected.
How does one do that? I cant find a way to do it without getting compiler errors.

On a sidenote, this part age(s.time.max.get, s.time.min.get)) yields me a deprecated warning I should use Query().min, but code like age(Query(s.time).max.get, Query(s.time).min.get) give this sql, which has an invalid syntay: age(select max(x6."timepoint"),select min(x6."timepoint")

[Colin Clausen]

So while I don't mind somebody asking this question just for reference I don't need help with this anymore. Decided to look for another framework. Slick just doesn't work out well with the project at hand. Too many super complex queries to do.

Sorry.

[Naftoli Gugenheim]

Perhaps you'd find sqltyped interesting?

--
 
---
You received this message because you are subscribed to the Google Groups "Slick / ScalaQuery" group.
To unsubscribe from this group and stop receiving emails from it, send an email to scalaquery+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[virtualeyes]

My thought exactly (or dropping down to string-based queries in Slick)

[Colin Clausen]

I had a look at sqltyped and a few other solutions and decided to try http://www.jooq.org 

Turns out that was a _great_ choice. It is exactly what I need: The ability to create sql queries in a programmatic way without losing the power of sql and without having to juggle with strings.

[Christopher Vogt]

Hi Colin,

if you look for something that feels and behaves like SQL, then indeed
jooq or something like it is what you are looking for. What Slick offers
is basically to run Scala code on your database engine to query your
data (or as close as we can get up until now).

Slick will make sense when you think about what Scala
collection code you would have to write to query a List[Game]. Here is
the code you were looking for:

def datePart(c: Column[String], c2: Column[Option[Timestamp]]) =
SimpleFunction[Option[Double]]("date_part").apply(Seq(c,c2))

val q4 = (for {

s <- Datapoints
r <- s.playerGame if (!r.locked && r.playerId === 410)
g <- r.game

} yield (g.id, s.time)).groupBy(_._1).map{ case (gid,groups) =>
datePart("epoch", age(groups.map(_._2).max,
groups.map(_._2).min)).asColumnOf[Long] * 1000L
}.sum

Notice the differences to the code you tried:

val q = (for {
s <- Datapoints
r <- s.playerGame if (!r.locked && r.playerId === 410)
g <- r.game
} yield (g.id, datePartLong("epoch", age(s.time.max.get,
s.time.min.get)) * 1000L)).groupBy(x => x._1).map {
case (id, len) => len.map(_._2).sum
}

There are understandable mistakes when your mind is (still) set on SQL.
You used (deprecated) aggregation functions .max/.min on columns and
used groupBy AFTER that. That's the SQL syntax, but it works differently
in Scala collections, where you write groupBy first and then aggregate
the grouped results (which is not deprecated). Hence your query lead to
invalid SQL as you experienced ("column "x4.id" must appear in the GROUP
BY clause or be used in an aggregate function"). In Slick 2.0 the
methods have been removed which prevents this mistake at compile time.

Also you call `.max.get` which is not certain to be valid as .max may
well return NULL, so Slick throws a "scala.slick.SlickException: Caught
exception while computing default value for Rep[Option[_]].getOrElse --
This cannot be done lazily when the value is needed on the database
side" because it figured that the value would have been needed on the
server side. (Message in 1.x may have been different). I created a
ticket, so we can review if we can improve the situation by a better
error message or clearer api: https://github.com/slick/slick/issues/549

The correct way in your case would have been not using .get and defining
the date_part method taking Option types, because that is the actual SQL
semantics, where functions operate on either a value or NULL and return
a value or NULL accordingly.

Also there have been several groupBy bug fixes in 2.0 compared to 1.x
which you may have run into. That paired with the problems you got
because of "thinking SQL" when using Slick made it understandably a bit
hard. We are constantly working on making Slick more robust against
strange behavior when used incorrectly, which is an ongoing effort.

In any case be assured, that we take negative feedback very seriously
and try to improve the situation.

Good luck with jooq and check back if you are ever annoyed by SQL
semantics and want Scala back :).

Chris

[Lukas Eder]

Hi there,

Since we're talking about jOOQ and even more about SQL, please allow me to chime in. :-)

Comments inline:

Let me comment on the following:

 

That paired with the problems you got
because of "thinking SQL" when using Slick made it understandably a bit
hard.

I find this point of view quite remarkable. SQL has come a long way and it does carry its 30-year-old legacy. But with legacy comes traction and robustness, and up to date, SQL has been the unchallenged means of querying and interoperating with RDBMS. Some non-RDBMS have even started to adopt SQL-esque languages as their querying interfaces because of the simplicity and expressivity of SQL. Not even LINQ-to-SQL can make such a claim, even if LINQ is a very well-adopted API for "querying" collections through LINQ-to-Objects, or entities through LINQ-to-EF.

Back to SQL: If Slick users care about their RDBMS' performance (which they should, when they pay millions for Oracle licenses and [xx] for Slick driver licenses), then they might really want to stay in control of aforementioned SQL. Now, if "thinking SQL" makes using Slick hard - or vice versa, using Slick makes "thinking SQL" hard, then maybe we have a new impedance mismatch here? Wouldn't it be better to implement your own "NoSQL" storage engine, then?

What is Typesafe's official stand on the "SQL subject"? Is that documented somewhere? Is "annoyance" with SQL an authoritative claim by Typesafe (as can be seen below)?

 

[...] check back if you are ever annoyed by SQL

semantics and want Scala back :).

Cheers

Lukas

[virtualeyes]

JOOQ has a near ideal syntax, but is still a bit busy compared to the elegance of LINQ-to-SQL which really strips it down to the bare essentials. 

Certainly possible in Scala but all the innovation is happening here with Slick, and the approach is functional a la Scala, not functional a la SQL ;-)

Am looking forward to the next couple of years in Scala, and on the JVM in general, good times ahead...

Happy 2014!

[Christopher Vogt]

To be clear: Slick is a joint project of EPFL and Typesafe and I am
working on the EPFL side. The point of view described in my posts are my
personal ones, which may or may not match those of Typesafe.

> That paired with the problems you got
> because of "thinking SQL" when using Slick made it understandably a bit
> hard.
>
> I find this point of view quite remarkable. SQL has come a long way and
> it does carry its 30-year-old legacy. But with legacy comes traction and
> robustness, and up to date, SQL has been the unchallenged means of
> querying and interoperating with RDBMS. Some non-RDBMS have even started
> to adopt SQL-esque languages as their querying interfaces because of the
> simplicity and expressivity of SQL. Not even LINQ-to-SQL can make such a
> claim, even if LINQ is a very well-adopted API for "querying"
> collections through LINQ-to-Objects, or entities through LINQ-to-EF.

The adoption of SQL-esque languages may be just due to the elegance of
comprehensions in general and the fact that SQL is the most
popular/familiar comprehension-language. Having a familiar API is a
strong feature. API-wise Jooq is familiar to SQL users and Slick is
familiar to Scala collections users. SQL and Scala comprehensions /
collection methods have a lot of closely corresponding concepts. But
there are syntactic and semantic differences in the APIs. Using one API
with having a different one in mind can lead to unexpected errors or
behavior. That was my point. If you are "thinking SQL" the Scala
collections API may behave unexpectedly and vise-versa.

> Back to SQL: If Slick users care about their RDBMS' performance (which
> they should, when they pay millions for Oracle licenses and [xx] for
> Slick driver licenses), then they might really want to stay in control
> of aforementioned SQL.

There are cases were hand tuning SQL is absolutely necessary. There are
other cases where Slick produces optimal or good enough SQL and
composability becomes more important because of the improved
productivity, understandability and more likely correctness that comes
with it. I am not familiar enough with jooq to know if it offers the
same level of composability. Monolithic SQL strings clearly do not.

> Now, if "thinking SQL" makes using Slick hard -
> or vice versa, using Slick makes "thinking SQL" hard, then maybe we
> have a new impedance mismatch here? Wouldn't it be better to
> implement your own "NoSQL" storage engine, then?

It is an API mismatch that makes it hard, NOT an impedance mismatch. SQL
and Slick have different APIs, but very similar concepts. Both are
relational... no mismatch.

> [...] check back if you are ever annoyed by SQL
> semantics and want Scala back :).
>
> What is Typesafe's official stand on the "SQL subject"? Is that
> documented somewhere? Is "annoyance" with SQL an authoritative claim by
> Typesafe (as can be seen below)?

You are overrating this "statement". This is just me being enthusiastic
about Slick offering a Scala-collections-like API. It did not mean that
an SQL-inspired API is bad or necessarily annoying. I respect jooq a
lot. If you want a type-safe SQL-like API, something like jooq is the
way to go. I said that in my recommendation to the initial poster of
this thread. But I do believe that there is a certain appeal in using
largely identical APIs for querying databases and collections and NOT
having two APIs (collections and SQL) to logically do the same thing
just on different storage. This is one of the things Slick is about. And
if familiarity with SQL is more important to someone than API
unification then that is perfectly fine and jooq is probably a good
library for that choice.

Slick embraces language integration, jooq embraces SQL familiarity. Both
embrace type-safe, relational queries. I see us largely fighting for the
same cause, offering something that can be superior to SQL strings and
ORMs. Let's focus on that :)!

Christopher

[Lukas Eder]

Hi Christopher,

Thank you very much for your response!

2014/1/21 Christopher Vogt <jan.christ...@gmail.com>

To be clear: Slick is a joint project of EPFL and Typesafe and I am
working on the EPFL side. The point of view described in my posts are my
personal ones, which may or may not match those of Typesafe.

>     That paired with the problems you got
>     because of "thinking SQL" when using Slick made it understandably a bit
>     hard.
>
> I find this point of view quite remarkable. SQL has come a long way and
> it does carry its 30-year-old legacy. But with legacy comes traction and
> robustness, and up to date, SQL has been the unchallenged means of
> querying and interoperating with RDBMS. Some non-RDBMS have even started
> to adopt SQL-esque languages as their querying interfaces because of the
> simplicity and expressivity of SQL. Not even LINQ-to-SQL can make such a
> claim, even if LINQ is a very well-adopted API for "querying"
> collections through LINQ-to-Objects, or entities through LINQ-to-EF.

The adoption of SQL-esque languages may be just due to the elegance of
comprehensions in general and the fact that SQL is the most
popular/familiar comprehension-language. Having a familiar API is a
strong feature. API-wise Jooq is familiar to SQL users and Slick is
familiar to Scala collections users. SQL and Scala comprehensions /
collection methods have a lot of closely corresponding concepts. But
there are syntactic and semantic differences in the APIs. Using one API
with having a different one in mind can lead to unexpected errors or
behavior. That was my point. If you are "thinking SQL" the Scala
collections API may behave unexpectedly and vise-versa.

Alright, point taken.

 

> Back to SQL: If Slick users care about their RDBMS' performance (which
> they should, when they pay millions for Oracle licenses and [xx] for
> Slick driver licenses), then they might really want to stay in control
> of aforementioned SQL.

There are cases were hand tuning SQL is absolutely necessary. There are
other cases where Slick produces optimal or good enough SQL and
composability becomes more important because of the improved
productivity, understandability and more likely correctness that comes
with it. I am not familiar enough with jooq to know if it offers the
same level of composability. Monolithic SQL strings clearly do not.

> Now, if "thinking SQL" makes using Slick hard -
> or vice versa, using Slick makes "thinking SQL" hard, then maybe we
> have a new impedance mismatch here? Wouldn't it be better to
> implement your own "NoSQL" storage engine, then?

It is an API mismatch that makes it hard, NOT an impedance mismatch. SQL
and Slick have different APIs, but very similar concepts. Both are
relational... no mismatch.

Would be interesting to see where this term came from. Clearly it is related to impedance matching:

http://en.wikipedia.org/wiki/Impedance_matching

I guess it can be said that Slick is indeed "maximising power transfer" by being a "relational-based" API, if we want to call it that way. So, OK, I can see your point. I won't reuse the impedance term again in this context.

>     [...] check back if you are ever annoyed by SQL
>     semantics and want Scala back :).
>
> What is Typesafe's official stand on the "SQL subject"? Is that
> documented somewhere? Is "annoyance" with SQL an authoritative claim by
> Typesafe (as can be seen below)?

You are overrating this "statement". This is just me being enthusiastic
about Slick offering a Scala-collections-like API. It did not mean that
an SQL-inspired API is bad or necessarily annoying.

OK, if that's what you say, then I clearly overrated this statement.

 

I respect jooq a
lot. If you want a type-safe SQL-like API, something like jooq is the
way to go. I said that in my recommendation to the initial poster of
this thread. But I do believe that there is a certain appeal in using
largely identical APIs for querying databases and collections and NOT
having two APIs (collections and SQL) to logically do the same thing
just on different storage. This is one of the things Slick is about. And
if familiarity with SQL is more important to someone than API
unification then that is perfectly fine and jooq is probably a good
library for that choice.

Slick embraces language integration, jooq embraces SQL familiarity. Both
embrace type-safe, relational queries. I see us largely fighting for the
same cause, offering something that can be superior to SQL strings and
ORMs. Let's focus on that :)!

Well, we can clearly agree on the latter :-)

Thanks

Lukas

[Christopher Vogt]

Hi Lukas,

> Thank you very much for your response!

likewise :).

> It is an API mismatch that makes it hard, NOT an impedance mismatch. SQL
> and Slick have different APIs, but very similar concepts. Both are
> relational... no mismatch.
>
> Would be interesting to see where this term came from. Clearly it is
> related to impedance matching:
> http://en.wikipedia.org/wiki/Impedance_matching
>
> I guess it can be said that Slick is indeed "maximising power transfer"
> by being a "relational-based" API, if we want to call it that way. So,
> OK, I can see your point. I won't reuse the impedance term again in this
> context.

I interpret impedance mismatch as the difference in concepts between OO
and SQL, especially imperative vs. declarative, but also object-identity
vs. primary keys, object-references vs. joins, an so forth.

> Slick embraces language integration, jooq embraces SQL familiarity. Both
> embrace type-safe, relational queries. I see us largely fighting for the
> same cause, offering something that can be superior to SQL strings and
> ORMs. Let's focus on that :)!
>
>
> Well, we can clearly agree on the latter :-)

Great :). We could even consider some kind of integration at some point.
I could imagine falling back to jooq instead of plain SQL when you hit a
Slick limitation. Or using Slick as a jooq backend to run jooq on nosql
stores once we get there with Slick (which is on hold at the moment due
to higher priorities). Slick can emulate relational features (e.g.
joins) when not supported by a backend natively, which could help. Maybe
we run into each other at Scala Days Berlin?

Chris

[Lukas Eder]

2014-01-23 Christopher Vogt <jan.christ...@gmail.com>

Hi Lukas,

> Thank you very much for your response!

likewise :).

>    It is an API mismatch that makes it hard, NOT an impedance mismatch. SQL
>     and Slick have different APIs, but very similar concepts. Both are
>     relational... no mismatch.
>
> Would be interesting to see where this term came from. Clearly it is
> related to impedance matching:
> http://en.wikipedia.org/wiki/Impedance_matching
>
> I guess it can be said that Slick is indeed "maximising power transfer"
> by being a "relational-based" API, if we want to call it that way. So,
> OK, I can see your point. I won't reuse the impedance term again in this
> context.

I interpret impedance mismatch as the difference in concepts between OO
and SQL, especially imperative vs. declarative, but also object-identity
vs. primary keys, object-references vs. joins, an so forth.

Yes, that's probably it. I always seem to find a way back to this essence. The fact that OO (and XML, etc.) define relationships "top-down", i.e. the parent references its children, whereas relational models define relationships "bottom-up", i.e. children reference their parents.

Erik Meijer and Gavin Bierman have spelled it out nicely:

http://queue.acm.org/detail.cfm?id=1961297

 

>     Slick embraces language integration, jooq embraces SQL familiarity. Both
>     embrace type-safe, relational queries. I see us largely fighting for the
>     same cause, offering something that can be superior to SQL strings and
>     ORMs. Let's focus on that :)!
>
>
> Well, we can clearly agree on the latter :-)

Great :). We could even consider some kind of integration at some point.
I could imagine falling back to jooq instead of plain SQL when you hit a
Slick limitation.

That sounds very interesting indeed! For 2014, we had planned to implement a native Scala API for jOOQ (ScOOQ?) to leverage all the nice Scala language / libraries features. Maybe it would even make sense to work together on a common API, or a bridge. We have similar licensing strategies, so I think that such a cooperation could be fruitful.

 

Or using Slick as a jooq backend to run jooq on nosql
stores once we get there with Slick (which is on hold at the moment due
to higher priorities).

I currently don't see that happening as jOOQ is really about SQL. Supporting NoSQL databases in jOOQ would introduce what Joel Spolsky called leaky abstractions:

http://www.joelonsoftware.com/articles/leakyabstractions.html

This isn't true for Slick, of course, which can perfectly map its Scala APIs onto NoSQL

 

Slick can emulate relational features (e.g.
joins) when not supported by a backend natively, which could help.

Yes, we share that strategy. A jOOQ example:

http://blog.jooq.org/2013/05/03/sql-query-transformation-fun-predicates-with-row-value-expressions/

 

Maybe we run into each other at Scala Days Berlin?

Maybe... I'll submit a talk for the CFP. Let's see if it's not off-topic

[Christopher Vogt]

> Erik Meijer and Gavin Bierman have spelled it out nicely:
> http://queue.acm.org/detail.cfm?id=1961297

Thx, I'll check it out.

> That sounds very interesting indeed! For 2014, we had planned to
> implement a native Scala API for jOOQ (ScOOQ?) to leverage all the nice
> Scala language / libraries features. Maybe it would even make sense to
> work together on a common API, or a bridge. We have similar licensing
> strategies, so I think that such a cooperation could be fruitful.

Sure, let's get in touch, when you guys are starting to work on this and
see where we can benefit from each other.

Chris

[Naftoli Gugenheim]

Maybe I missed something, but us there a reason no one mentioned the other SQL-style Scala libraries, like Squeryl and sqltyped? I believe there are others besides jooq as well.

--

---
You received this message because you are subscribed to the Google Groups "Slick / ScalaQuery" group.
To unsubscribe from this group and stop receiving emails from it, send an email to scalaquery+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/scalaquery/52EA18BB.7010502%40gmail.com.

[Christopher Vogt]

> Maybe I missed something, but us there a reason no one mentioned the
> other SQL-style Scala libraries, like Squeryl and sqltyped? I believe
> there are others besides jooq as well.

Lukas works in jooq, I work on Slick, that's why we talked about these.
There are indeed other libraries out there.

Chris