Hi,
I have a fairly complex join query that I wrote in native SQL (using mysql) that I am trying to rewrite in standard HQL to be db agnostic but am having a lot of difficulties. For starters, HQL does not seem to support limit statements other than in query.criteria settings, but I have to join against a sub-select with a limit. So I really am not sure how to accomplish this.
Can anyone help please?
Code:
select * from player as p join (
select p.last_login, ps.player_id, count(sv.hit) as total_votes, sum(sv.hit) as votes, sum(sv.hit)/count(sv.hit)*100 as percentage, count(sv.hit) + sum(sv.hit)/count(sv.hit) as score
from snapshot as s join snapshot_vote as sv on (s.id = sv.snapshot) join player_snapshot as ps on (s.id=ps.snapshot_id) join player p on (ps.player_id = p.id)
group by ps.player_id
order by score desc limit 0, :topPlayerPoolSize
) as topScores
having last_login > date_sub( now(), interval :lastLoginDays day ) and player_id not in
(select player_id from trend_leader_article where date_issued > date_sub( now(), interval :lastTrendLeaderMonths month ) )
order by rand() limit 0,1;
Any help or suggestions are would be greatly appreciated!
Thanks,
Eric
Login
FAQ
Search