Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » help with a ORDER BY COUNT (expression builder and reportquery)
icon2.gif  help with a ORDER BY COUNT (expression builder and reportquery) [message #634750] Sat, 23 October 2010 00:34 Go to next message
brad Mising name is currently offline brad Mising nameFriend
Messages: 17
Registered: February 2010
Junior Member
Hello all, this may be simple but after fumbling around with it for a bit I have to come to you for help. What I am trying to do:

SELECT COUNT(code)
FROM table
GROUP BY code
ORDER BY count DESC
LIMIT 100

So far my code looks like:

Expression expression = new ExpressionBuilder();
ReportQuery rq = new ReportQuery(class, expression);
rq.addGrouping("code");
rq.addCount("code");
rq.addOrdering(expression.get("count").descending());
rq.setMaxRows(100);

executeQuery(rq);

The issue is that eclipselink is complaining there is no such column as "count" and blows up. Any thoughts or tips? Thanks
-Brad

Re: help with a ORDER BY COUNT (expression builder and reportquery) [message #635084 is a reply to message #634750] Mon, 25 October 2010 15:23 Go to previous messageGo to next message
brad Mising name is currently offline brad Mising nameFriend
Messages: 17
Registered: February 2010
Junior Member
tried adding:

Expression countExpression = new ExpressionBuilder();
countExpression.get("code").count();
rq.addItem("count",countExpression);

to try and get a column with counts but this didn't seem to work. Any advice?

Thanks
-Brad
Re: help with a ORDER BY COUNT (expression builder and reportquery) [message #635087 is a reply to message #635084] Mon, 25 October 2010 15:32 Go to previous messageGo to next message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

The issue seems to be your orderBy, try,

Expression expression = new ExpressionBuilder();
ReportQuery rq = new ReportQuery(class, expression);
rq.addGrouping("code");
rq.addCount("code");
rq.addOrdering(expression.get("code").count().descending());
rq.setMaxRows(100);

If still having problems please include the error.

You can also always use native SQL.


James : Wiki : Book : Blog : Twitter
Re: help with a ORDER BY COUNT (expression builder and reportquery) [message #635110 is a reply to message #635087] Mon, 25 October 2010 16:22 Go to previous message
brad Mising name is currently offline brad Mising nameFriend
Messages: 17
Registered: February 2010
Junior Member
Thank you very much! Your solution worked well.

-Brad
Previous Topic:Eclipselink query optimization problem
Next Topic:Query.setMaxResults(0) seems to fail
Goto Forum:
  


Current Time: Thu Dec 18 22:34:55 GMT 2014

Powered by FUDForum. Page generated in 0.01822 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software