Eclipse Community Forums: EclipseLink » help with a ORDER BY COUNT (expression builder and reportquery)

Help

Home

Home » Eclipse Projects » EclipseLink » help with a ORDER BY COUNT (expression builder and reportquery)

Show: Today's Messages :: Show Polls :: Message Navigator

help with a ORDER BY COUNT (expression builder and reportquery) [message #634750]

Fri, 22 October 2010 20:34

brad Mising name
Messages: 14
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

Report message to a moderator

Re: help with a ORDER BY COUNT (expression builder and reportquery) [message #635084 is a reply to message #634750]

Mon, 25 October 2010 11:23

brad Mising name
Messages: 14
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

Report message to a moderator

Re: help with a ORDER BY COUNT (expression builder and reportquery) [message #635087 is a reply to message #635084]

Mon, 25 October 2010 11:32

James Sutherland
Messages: 1834
Registered: July 2009

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

Report message to a moderator

Re: help with a ORDER BY COUNT (expression builder and reportquery) [message #635110 is a reply to message #635087]

Mon, 25 October 2010 12:22

brad Mising name
Messages: 14
Registered: February 2010

Junior Member

Thank you very much! Your solution worked well.

-Brad

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 19 00:30:10 EDT 2013