| Subqueries using ExpressionBuilder [message #642676] |
Thu, 02 December 2010 05:15  |
Eclipse User |
|
|
|
Hi,
we need to execute a query that should return a list of entities and a calculated value and that executes a filter on that calculated value. We developed the following code that executes this query using ExpressionBuilder:
public void testExecuteQuery()
{
ExpressionBuilder mainEb = new ExpressionBuilder();
ReportQuery mainQuery = new ReportQuery(LocazioneFisica.class, mainEb);
mainQuery.addAttribute("main", mainEb);
mainQuery.addAttribute("occupata", creaOccupataExpr(mainEb));
mainQuery.setSelectionCriteria(creaOccupataExpr(mainEb).equal("true"));
List<ReportQueryResult> resultList = (List<ReportQueryResult>) ((JpaEntityManager) entityManager.getDelegate()).getActiveSession()
.executeQuery(mainQuery);
}
private Expression creaOccupataExpr(ExpressionBuilder mainEb)
{
ExpressionBuilder subEb = new ExpressionBuilder();
ReportQuery subQuery = new ReportQuery(Contenitore.class, subEb);
subQuery.addAttribute("id");
subQuery.setSelectionCriteria(subEb.get("locazioneFisica").equal(mainEb));
Map<Expression, Object> map = new HashMap<Expression, Object>();
map.put(mainEb.get("area").notNull(), "false");
map.put(mainEb.exists(subQuery), "true");
Expression occupataExpr = mainEb.caseConditionStatement(map, "false");
return occupataExpr;
}
The problem is that the subSelect is executed two times, once in the result clause and once in the selection criteria. Is it possible to perform the same query with a single execution of subselect?
We would need a method to add an external query of a reportQuery, something like:
select * from (
select obj.*, calculatedValue from ....
)
where calculatedValue is not null
Thanks in advance
Best regards, Fabio
|
|
|
| Re: Subqueries using ExpressionBuilder [message #642805 is a reply to message #642676] |
Thu, 02 December 2010 13:22  |
| Eclipse User |
|
|
|
Sub-selects in the from clause are not currently supported. You could log an enhancement request. I'm actually surprised that a sub-select in the select clause worked.
If you need this type of query you can use native SQL.
|
|
|
Powered by
FUDForum. Page generated in 0.10328 seconds
Home