Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Eclipse link Groups by on embedded entity rather than on the root entity in Criteria Query(Criteria query issue in eclipse link with embedded entity in group by clause.)
icon5.gif  Eclipse link Groups by on embedded entity rather than on the root entity in Criteria Query [message #1751412] Tue, 10 January 2017 06:58
sachin joshi is currently offline sachin joshiFriend
Messages: 1
Registered: January 2017
Junior Member
Hi i have an entity as Suitetestcasemapping which has many to one relationship with Suite entity attached herewith are both the entities and their corresponding metamodels. I have the following criteria query.

CriteriaQuery<Integer> suitesToBeDeleted = criteriaBuilder.createQuery(Integer.class);
Root<Suitetestcasemapping> suiteTestcaseMappingRoot = suitesToBeDeleted.from(Suitetestcasemapping.class);
suitesToBeDeleted.groupBy(suiteTestcaseMappingRoot.get(Suitetestcasemapping_.suite).get(Suite_.suiteid));
Expression<Long> allFlagCount = criteriaBuilder.count(suiteTestcaseMappingRoot.get(Suitetestcasemapping_.flag));

Subquery<Integer> countOfDeleteSuiteTestcasemappings = suitesToBeDeleted.subquery(Integer.class);
Root<Suitetestcasemapping> subQuerySuiteTestcaseMappingRoot = countOfDeleteSuiteTestcasemappings.from(Suitetestcasemapping.class);
Predicate sameSuitePredicate = criteriaBuilder.equal(suiteTestcaseMappingRoot.get(Suitetestcasemapping_.suite), subQuerySuiteTestcaseMappingRoot.get(Suitetestcasemapping_.suite));
Predicate deletedMappingPredicate = criteriaBuilder.equal(subQuerySuiteTestcaseMappingRoot.get(Suitetestcasemapping_.flag), 4);
countOfDeleteSuiteTestcasemappings.where(sameSuitePredicate,deletedMappingPredicate);
Expression countOfDeletedFlagExpression = criteriaBuilder.count(subQuerySuiteTestcaseMappingRoot.get(Suitetestcasemapping_.flag));
countOfDeleteSuiteTestcasemappings.select(countOfDeletedFlagExpression);
suitesToBeDeleted.having(criteriaBuilder.equal(allFlagCount, countOfDeleteSuiteTestcasemappings));
suitesToBeDeleted.select(suiteTestcaseMappingRoot.get(Suitetestcasemapping_.suite).get(Suite_.suiteid));
TypedQuery<Integer> query1 = entityManager.createQuery(suitesToBeDeleted);
List<Integer> dataList = query1.getResultList();

The following query is generated from the above criteria by Eclipse link.
SELECT t0.SUITEID FROM suites t0, SUITETESTCASEMAPPING t1 WHERE (t0.SUITEID = t1.suiteid) GROUP BY t0.SUITEID HAVING (COUNT(t1.FLAG) = (SELECT COUNT(t2.FLAG) FROM suites t3, SUITETESTCASEMAPPING t2 WHERE (((t1.suiteid = t3.SUITEID) AND (t2.FLAG = 4)) AND (t3.SUITEID = t2.suiteid))));

The group by condition is t0.SUITEID whereas it should have been on t1.SUITEID. I don't know whether it is problem with my code or it is a Bug with Eclipse Link.

A valid native query for the above scenario is given below:
SELECT t1.SUITEID FROM SUITETESTCASEMAPPING t1 GROUP BY t1.SUITEID HAVING (COUNT(t1.FLAG) =(SELECT COUNT(t2.FLAG) FROM SUITETESTCASEMAPPING t2 WHERE ( t2.FLAG = 4 and t1.suiteid=t2.suiteid)))
Previous Topic:The DB2 platform/dialect is inefficient, out of date and inexact
Next Topic:Issue with EclipseLink entities
Goto Forum:
  


Current Time: Thu Dec 14 10:23:58 GMT 2017

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

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