-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 15 posts ] 
Author Message
 Post subject: HQL: Can't "group by" object
PostPosted: Thu Nov 24, 2005 2:45 am 
Beginner
Beginner

Joined: Fri Nov 04, 2005 3:51 pm
Posts: 32
Hibernate version: 3.1

Hi
Anybody know why I can't do this with hql?
(I know the given query is pointless, but it's a generic example of my real one)
Code:
select obj from MyClass as obj group by obj


It give me error like
Code:
Column 'obj0_.fork_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


The behavior I would expect from that query is for it to group by the class's mapped identifier and return the objects.

The following does not error, but is not what I want as it returns only id, not object
Code:
select obj.id from MyClass as obj group by obj.id


The following snippet from the documenation gives me the impression that I should be retrieve object grouped by object.
Code:
select cat
from Cat cat
    join cat.kittens kitten
group by cat
having avg(kitten.weight) > 100
order by count(kitten) asc, sum(kitten.weight) desc


Please help.
Thanks you


Top
 Profile  
 
 Post subject:
PostPosted: Thu Nov 24, 2005 10:48 am 
Newbie

Joined: Thu Nov 17, 2005 12:59 pm
Posts: 18
try the following:
from MyClass obj group by obj.id


Top
 Profile  
 
 Post subject:
PostPosted: Thu Nov 24, 2005 2:27 pm 
Beginner
Beginner

Joined: Fri Nov 04, 2005 3:51 pm
Posts: 32
Thank you for suggestion. But I have already tried that and it give me same error. Does a query like that work for you? If so, I wonder if it because different database. I use sql 2000.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Nov 25, 2005 4:19 am 
Beginner
Beginner

Joined: Fri Nov 04, 2005 3:51 pm
Posts: 32
Does this work for anybody?
Code:
select cat from Cat cat group by cat

It really important for me.

Even if I did,
Code:
select cat.id from Cat cat group by cat.id

it seem like there could be a way to retrieve object instead of only id. Something like,
Code:
select load(cat.id) from Cat cat group by cat.id


Top
 Profile  
 
 Post subject:
PostPosted: Fri Nov 25, 2005 4:39 am 
Beginner
Beginner

Joined: Tue Nov 22, 2005 4:53 pm
Posts: 41
Location: Netherlands
franknelsen wrote:
Does this work for anybody?
Code:
select cat from Cat cat group by cat

it seem like there could be a way to retrieve object instead of only id.

If you don't specify a select, than it will alyways load the objects.

Code:
"from Object obj"


Also this is already grouped by Object, because all objects are unique.

You only need and can specify a group by object when you use an aggregation on a relation of object.
i.e.:
Code:
"from Object o where max(o.childObjectValue) > value group by obj"


Top
 Profile  
 
 Post subject:
PostPosted: Fri Nov 25, 2005 12:58 pm 
Beginner
Beginner

Joined: Fri Nov 04, 2005 3:51 pm
Posts: 32
momania, thank for reply. But, if you read my original post, it say this is only a simplified query (I know it is not useful by itself) that represents the problem. The real query is much more complex, but if you look at this example from the documentation you see that they are doing something similar. The object is in the select list (non-aggregated) and is also grouped. Does this work for anyone?

Code:
select cat
from Cat cat
    join cat.kittens kitten
group by cat
having avg(kitten.weight) > 100
order by count(kitten) asc, sum(kitten.weight) desc


Top
 Profile  
 
 Post subject:
PostPosted: Tue Nov 29, 2005 4:06 am 
Beginner
Beginner

Joined: Tue Nov 22, 2005 4:53 pm
Posts: 41
Location: Netherlands
franknelsen wrote:
The object is in the select list (non-aggregated) and is also grouped.
Code:
select cat
from Cat cat
    join cat.kittens kitten
group by cat
having avg(kitten.weight) > 100
order by count(kitten) asc, sum(kitten.weight) desc

There is a aggregation, but it's in the order by.
That's what I was trying to explain you. As long as you don't use a join, aggregation, etc., you can not use the group by because it has no use. ;)

Maybe if you show us you more complex query, we can help you out with it ;)


Top
 Profile  
 
 Post subject:
PostPosted: Tue Nov 29, 2005 4:55 am 
CGLIB Developer
CGLIB Developer

Joined: Thu Aug 28, 2003 1:44 pm
Posts: 1217
Location: Vilnius, Lithuania
What is expected result of this query ? "select cat from Cat cat group by cat"


Top
 Profile  
 
 Post subject:
PostPosted: Thu Dec 01, 2005 2:11 am 
Beginner
Beginner

Joined: Fri Nov 04, 2005 3:51 pm
Posts: 32
Thank you both for trying help.

Quote:
What is expected result of this query ? "select cat from Cat cat group by cat"


I would expect the exact same as what you get from "select cat from Cat cat".


Quote:
Maybe if you show us you more complex query, we can help you out with it ;)

Ok, I'll try another example. If I showed original, it would take 3 pages to explain ;)

Objective:
Return all unique combinations of Cat and Girlfriend along with a count of how many toys are shared between the girlfriends and the kittens (for each Cat/Girlfriend combo).


This works:
Code:
select
   a.id,
   b.id,
   count(d)
from
   Cat a
left join
   a.girlfriends b
left join
   a.kittens c
left join
   c.toys d with d in elements(b.toys)
group by
   a.id,
   b.id
order
   by a.id,
   b.id




This one gives sql error:
Code:
select
   a,
   b,
   count(d)
from
   Cat a
left join
   a.girlfriends b
left join
   a.kittens c
left join
   c.toys d with d in elements(b.toys)
group by
   a,
   b



From my perspective, if the first one should work, then the second one should work as well. I try to do the second one, because I don't want just the object id's, I want the actual object. But Hibernate try to group on all of the class's columns which doesn't correlate to anything in the select list, so it fails.
I got the impression that my query should work because of the example I pointed out from the documentation. I still don't understand what is so different about mine.

I have worked around it for now by using native sql query. But I would still like to understand as I'm sure it will come up for me again.

thanks you


Top
 Profile  
 
 Post subject:
PostPosted: Thu Dec 01, 2005 3:39 am 
CGLIB Developer
CGLIB Developer

Joined: Thu Aug 28, 2003 1:44 pm
Posts: 1217
Location: Vilnius, Lithuania
Probably there is a mistake in example.
BTW it doe's not make sence to "group" if key is included (object always includes key) result will have no groups anyway, "group" makes sence for projection queries only.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Dec 01, 2005 3:54 am 
Beginner
Beginner

Joined: Fri Nov 04, 2005 3:51 pm
Posts: 32
Well essentially what I'm trying to do is something like "select distinct(a,b)". Since that is not legal, my understanding is to do it like "group by a,b". That gives me the results I'm looking for. Is there a better way?


Top
 Profile  
 
 Post subject:
PostPosted: Thu Dec 01, 2005 4:07 am 
CGLIB Developer
CGLIB Developer

Joined: Thu Aug 28, 2003 1:44 pm
Posts: 1217
Location: Vilnius, Lithuania
It is better to filter records using "where", but if you can not find the right predicate then postprocess results on client using HashSet


Top
 Profile  
 
 Post subject:
PostPosted: Thu Dec 01, 2005 4:12 am 
CGLIB Developer
CGLIB Developer

Joined: Thu Aug 28, 2003 1:44 pm
Posts: 1217
Location: Vilnius, Lithuania
BTW hibernate must not return duplicates from queries, it must be forced postprocess result by object identity management itself.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Dec 06, 2005 12:04 am 
Beginner
Beginner

Joined: Fri Nov 04, 2005 3:51 pm
Posts: 32
Ok.
Anyway, for another reference on what I was trying to accomplish. There is this link.

http://forum.hibernate.org/viewtopic.php?p=2273602&highlight=#2273602

Thanks again.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jan 06, 2006 6:11 pm 
Expert
Expert

Joined: Fri Oct 28, 2005 5:38 pm
Posts: 390
Location: Cedarburg, WI
Is there any solution to this problem? It's a showstopper for us ...


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 15 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.