-->
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.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 15 posts ] 
  Previous topic | Next topic 
Author Message
thomers
 Post subject: HQL syntax problem converting a working SQL query
PostPosted: Fri Feb 06, 2009 12:49 pm 
Newbie

Joined: Wed Oct 29, 2008 8:54 am
Posts: 13
I'm trying to convert an SQL query that works in mySQL into HQL.

The query returns the rank of a user's max score in a highscore list.

The original query:
Quote:
SELECT count( * ) +1 FROM (
SELECT max( gsd.earnedcoins ) , gsd.character_id
FROM gamesessiondetails gsd, gamesessions gs
WHERE gs.game_id =2
AND gsd.gamesession_id = gs.id
AND gsd.earnedcoins > 123
GROUP BY gsd.character_id
ORDER BY max( gsd.earnedcoins ) DESC , gs.enddate DESC
)
AS rank


the HQL query:
Quote:
select count(*) from (
select max(gsd.earnedCoins), gsd.character from GameSessionDetails gsd, GameSession gs
where (gs.game.id = 2)
and gsd.gameSession = gs
and gsd.earnedCoins > 123
group by gsd.character
order by max(gsd.earnedCoins) desc, gs.endDate desc
) as rank


gives me this exception:

Code:
SEVERE: line 1:22: unexpected token: (
Exception in thread "main" org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ( near line 1, column 22 [select count(*) from (select max(gsd.earnedCoins), gsd.character from net.plazz.atlantis.domain.GameSessionDetails gsd, net.plazz.atlantis.domain.GameSession gs where (gs.game.id = 2) and gsd.gameSession = gs and gsd.gameSession.endDate >= :fromdate and gsd.gameSession.endDate <= :todate and gsd.earnedCoins > 123 group by gsd.character order by max(gsd.earnedCoins) desc, gs.endDate desc ) as rank]
   at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:31)
   at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:24)
   at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:59)
   at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:258)
   at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:157)
   at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111)
   at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
   at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:56)
   at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72)
   at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)
   at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)
   at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1623)


according to the hibernate documentation, subselects should be enclosed in ( and ) . is there an easier way for this query?

Hibernate version:
3.3.1

Name and version of the database you are using:
mySQL 5.0.45

thanks,
thomas
Top
 Profile  
 
thomers
 Post subject:
PostPosted: Fri Feb 06, 2009 12:55 pm 
Newbie

Joined: Wed Oct 29, 2008 8:54 am
Posts: 13
or should i lose the COUNT(*), just use the subquery as query and count the number of rows i get like

query.list().size() ?

but in that case i would be instantiating the whole list (which does not make sense for long lists)
Top
 Profile  
 
pkleindl
 Post subject:
PostPosted: Sat Feb 07, 2009 6:28 am 
Senior
Senior

Joined: Thu Jan 08, 2009 3:48 pm
Posts: 168
As far as i understand your SQL you want to retrieve the number (count) of GameSession for a certain game_id and which earned more than 123 Coins
grouped by character and reduced to max-values

I think it should work with
Code:
select count(distinct gsd.character) from GameSessionDetails gsd
inner join gsd.gameSession gs
where gs.game_id =2
and gsd.earnedCoins > 123


Rating is welcome if that works
Top
 Profile  
 
thomers
 Post subject:
PostPosted: Mon Feb 09, 2009 4:57 am 
Newbie

Joined: Wed Oct 29, 2008 8:54 am
Posts: 13
perfect - thank you very much!
Top
 Profile  
 
thomers
 Post subject:
PostPosted: Mon Mar 02, 2009 12:19 pm 
Newbie

Joined: Wed Oct 29, 2008 8:54 am
Posts: 13
extending the previous example, how can i convert this working SQL query:

Code:
select count(*) from (

select sum(gsd.earnedcoins) as sumearnedcoins, gsd.character_id
from gamesessiondetails gsd
group by gsd.character_id
order by sum(gsd.earnedcoins) desc

) as earnedcoins

where sumearnedcoins > 50000


thanks in advance!
thomas
Top
 Profile  
 
pkleindl
 Post subject:
PostPosted: Mon Mar 02, 2009 1:40 pm 
Senior
Senior

Joined: Thu Jan 08, 2009 3:48 pm
Posts: 168
First your SQL should probably use a "having" clause instead of the where.
Second you dont need an order by if you are just going to count the results.

you probably want something like
Code:
select count(gsd) from gamesessiondetails gsd
group by gsd.character_id
having sum(gsd.earnedcoins)>50000


rating is welcome

Patrik
Top
 Profile  
 
thomers
 Post subject:
PostPosted: Mon Mar 02, 2009 4:32 pm 
Newbie

Joined: Wed Oct 29, 2008 8:54 am
Posts: 13
Quote:
select count(gsd) from gamesessiondetails gsd
group by gsd.character_id
having sum(gsd.earnedcoins)>50000


thanks, agreed.

what i want, is a ranking, how many characters have accumulated more earnedcoins (summed up from all their gamesessions) than i have (50000 in my example).

so, around that query, i need another select count(*) ?

or should i just use query.list().size() ?

would hibernate just get the count (size), or fetch all the rows from the DB?
Top
 Profile  
 
thomers
 Post subject:
PostPosted: Mon Mar 02, 2009 4:52 pm 
Newbie

Joined: Wed Oct 29, 2008 8:54 am
Posts: 13
note: this does not work:
Code:
            Query query = hibSession.createQuery(
                "select gsd.character, sum(gsd.earnedCoins) from GameSessionDetails gsd " +
                        "where sum(gsd.earnedCoins) > :mycoins " +
                        "group by gsd.character " +
                        "having sum(gsd.earnedCoins) > :myachievercoins");

            query.setParameter("mycoins", new Long(50000));

            List list = query.list();
            System.out.println(list.size());


gives me an SQLException: Invalid use of group function
Top
 Profile  
 
pkleindl
 Post subject:
PostPosted: Mon Mar 02, 2009 5:46 pm 
Senior
Senior

Joined: Thu Jan 08, 2009 3:48 pm
Posts: 168
drop the sum() in the where clause, the where happens before the group hence you cant reference sum() there

if you need both then both should be in the having clause
Top
 Profile  
 
thomers
 Post subject:
PostPosted: Tue Mar 03, 2009 4:17 am 
Newbie

Joined: Wed Oct 29, 2008 8:54 am
Posts: 13
thanks, silly mistake.

the question remains, whats the best practice to get the number of rows from this query?

Quote:
so, around that query, i need another select count(*) ?

or should i just use query.list().size() ?


Top
 Profile  
 
pkleindl
 Post subject:
PostPosted: Tue Mar 03, 2009 6:38 am 
Senior
Senior

Joined: Thu Jan 08, 2009 3:48 pm
Posts: 168
Code:
select count(gsd) from gamesessiondetails gsd
group by gsd.character_id
having sum(gsd.earnedcoins)>50000

gives you only the number of characters which have earned more than 50000 coins, what else do you want?

list.size() is not a good idea because then the whole collection is initialized

btw you can rate up to 3 times ;o)
Top
 Profile  
 
thomers
 Post subject:
PostPosted: Tue Mar 03, 2009 6:48 am 
Newbie

Joined: Wed Oct 29, 2008 8:54 am
Posts: 13
pkleindl wrote:
Code:
select count(gsd) from gamesessiondetails gsd
group by gsd.character_id
having sum(gsd.earnedcoins)>50000

gives you only the number of characters which have earned more than 50000 coins, what else do you want?


nope, that gives me the number of gamesessions for each character that has more than 50000 coins.

what i need it the total number of characters with a sum of more than 50000 coins.

Quote:
btw you can rate up to 3 times ;o)


i will :-)
Top
 Profile  
 
pkleindl
 Post subject:
PostPosted: Tue Mar 03, 2009 8:06 am 
Senior
Senior

Joined: Thu Jan 08, 2009 3:48 pm
Posts: 168
Something like

Code:
select count(c) from characters c inner join c.gamesessiondetails gsd
group by c.id
having sum(gsd.earnedcoins)>50000


maybe?

Thanks for the rating
Top
 Profile  
 
thomers
 Post subject:
PostPosted: Tue Mar 03, 2009 1:29 pm 
Newbie

Joined: Wed Oct 29, 2008 8:54 am
Posts: 13
thanks so much for your help - i'm still having problems - SQL:

Code:
SELECT count( c.id )
FROM characters c
INNER JOIN gamesessiondetails gsd ON gsd.character_id = c.id
GROUP BY c.id
HAVING sum( gsd.earnedcoins ) >50000


still gives me a count-value per character, not just one value.
Top
 Profile  
 
pkleindl
 Post subject:
PostPosted: Tue Mar 03, 2009 5:47 pm 
Senior
Senior

Joined: Thu Jan 08, 2009 3:48 pm
Posts: 168
Code:
SELECT sum(count(distinct c.id)) FROM...


just tried it on database, should work that way
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 15 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

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