Hibernate Community • View topic

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Max of row count

Page 1 of 1

[ 3 posts ]

Previous topic | Next topic

Author

Message

frode

Post subject: Max of row count

Posted: Mon May 21, 2007 6:45 pm

Newbie

Joined: Mon May 21, 2007 6:36 pm
Posts: 3

Hello,

I have the entities Contribution (ie a forum thread), Category and User. Now, I'd like to fetch the category in which a user has written most contributions. I've tried this:

Code:

SELECT cat, count(cat.id) AS am
FROM 
  Category cat, IN (cat.contributions) c
WHERE 
  c.user=:user
GROUP BY 
  cat.id
ORDER BY am DESC

but it will fail on ORDER BY am, telling me it's not a column.

I've also tried something like this

Code:

SELECT cat
FROM 
  Category cat, IN (cat.contributions) c
WHERE 
  c.user=:user
GROUP BY 
  cat.id
HAVING count(cat.id) = max(count(cat.id))

but this wont work either (illegal use of group function).

Can anyone help me out or give me a clue?

- Frode

Top

frode

Post subject:

Posted: Tue May 22, 2007 11:48 am

Newbie

Joined: Mon May 21, 2007 6:36 pm
Posts: 3

This query seems to do the job..

Code:

SELECT cat, count(cat.id) as am
FROM 
  Category cat, IN (cat.contributions) c
WHERE 
  c.user=:user
GROUP BY 
  cat.id
ORDER BY 2

Now the problem is how to get the Category object when the returned "row" is made up by the category and am (count).. hmm.

Top

frode

Post subject:

Posted: Tue May 22, 2007 12:52 pm

Newbie

Joined: Mon May 21, 2007 6:36 pm
Posts: 3

The answer was of course in TFM, which I was never any good at reading :-)

For anyone stumbling upon this thread in the future, wondering the same:

http://e-docs.bea.com/kodo/docs41/full/html/ejb3_langref.html#ejb3_langref_resulttype

When there is more than one element in the SELECT clause (ie "SELECT category, count(category.id) FROM ...."), a list of Object[] is returned with getResultList instead of a . In my case o[0] was the category, and o[1] was the count value.

Code:

List l = q.getResultList();
em.getTransaction().commit();
      
Object[] os;
Object o;
try {
   os = (Object[])l.get(0);
   o = os[0];
   logger.debug("The SELECT clause for this query had more than one element.");
}
catch(ClassCastException cce) {
   logger.debug("The SELECT clause for this query only had one element.");
   o = l.get(0);
}

Amazing how it helps to just explain the problem to yourself sometimes.

Top

Page 1 of 1

[ 3 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