Hibernate Community • View topic - how to order by the size of a collection?

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

how to order by the size of a collection?

Page 1 of 1

[ 6 posts ]

Previous topic | Next topic

Author

Message

danielx510

Post subject: how to order by the size of a collection?

Posted: Fri Nov 14, 2003 12:00 am

Newbie

Joined: Thu Oct 16, 2003 7:22 pm
Posts: 12

Basically I want to order a list of items by the size of a collection.
e.g. A has a one-to-many relationship with B

I want to retrieve a list of A ordered by the number of associated B's

I followed the instruction from the Hibernate User FAQ Tips and Tricks http://www.hibernate.org/118.html#A5 but it does not appear to work.

HQL:

Code:

SELECT edge, size(target.outgoingEdgeList)
  FROM Edge AS edge LEFT JOIN FETCH edge.target AS target
  WHERE edge.source.id = :source.id
  GROUP BY edge

this is the generated SQL:

Code:

select
  edge.id as id,
  edge.dateUpdated as dateUpda3_,
  edge.name as name,
  edge.dateDeleted as dateDele5_,
  edge.dateCreated as dateCrea8_
from tb_edge edge inner join tb_vertex vertex0_ on edge.target_id=vertex0_.id
where this.source_id = ? group by  edge.id

which generates the following error:

Code:

[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Column 'edge.dateUpdated' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Top

gavin

Post subject:

Posted: Fri Nov 14, 2003 1:39 am

Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia

You need to group by all the properties of edge.

Top

danielx510

Post subject:

Posted: Fri Nov 14, 2003 1:54 am

Newbie

Joined: Thu Oct 16, 2003 7:22 pm
Posts: 12

understood, but is this the intended behavior or is it a bug? it looks like from the Hiberate FAQ that

Code:

...GROUP BY Edge

should work...?

In particular, this was the example given in the FAQ:

Code:

select user 
from User user
left join user.messages msg
group by user 

Top

gavin

Post subject:

Posted: Fri Nov 14, 2003 3:14 am

Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia

It is intended. We plan to improve this situation in a backward-compatible way after 2.1. We talked about it in Aarhus. There is a Good Solution :)

Top

franknelsen

Post subject:

Posted: Mon Nov 28, 2005 1:30 am

Beginner

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

Quote:

It is intended. We plan to improve this situation in a backward-compatible way after 2.1. We talked about it in Aarhus. There is a Good Solution :)

What is the solution?

Top

mpopetz

Post subject:

Posted: Fri Nov 24, 2006 12:21 pm

Beginner

Joined: Tue Oct 28, 2003 12:09 pm
Posts: 46

I've gotten around this by selected cat.id, grouping by cat.id and then doing a hibernate.load in the java code when iterating the results.

My example may be a bit complicated, but here it is:

Code:

select entry.module.id, entry.sessionNumber, count(entry) from PageViewLogEntry entry where entry.sectionRole.id = ? 
and  entry.module is not null and entry.sessionNumber is not null 
group by entry.module.id, entry.sessionNumber

and then do:

Code:

        
for(Object[] result : results)
{
      Module module = (Module) session.load(Module.class, (Long)result[0]);

hope that helps.

Top

Page 1 of 1

[ 6 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

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