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]

Sat, 23 October 2010 00:34

brad Mising name

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

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 15:23

brad Mising name

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

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 15:32

James Sutherland

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

Report message to a moderator