| Criteria API + countDistinct + composite primary key [message #540813] |
Thu, 17 June 2010 08:39  |
Eclipse User |
|
|
|
Hi,
we use criteria API from JPA 2.0. We want to perform count with distinct.
In JPQL something like this:
SELECT count distinct (entity) from Entity entity.
With criteria API this:
CriteriaBuilder b = entityManager.getCriteriaBuilder();
CriteriaQuery q = b.createQuery(Long.class);
Root root = q.from(Entity.class);
root.alias("entity");
q.select(b.countDistinct(root));
entityManager.createQuery(q).getSimpleResult();
If we use entity with simple primary key, it works correctly but we have
composite primary key and there is some problem.
We have Entity which is association Entity between Group and User. We can called it
GroupUser. GroupUser has composite primary key composed by primary key of Group and User.
One User has many GroupUser. We try to find count distinct of GroupUser which belongs to one User.
If we perform our query we get this SQL command in console:
FROM (SELECT COUNT(t0.USER_CODE) FROM GROUPUSER t3
LEFT OUTER JOIN USER t1 ON (t1.USER_CODE = t3.USER_CODE), GROUPUSER t0
WHERE EXISTS (SELECT t3.USER_CODE FROM GROUPUSER t3 WHERE (((t0.USER_CODE = t3.USER_CODE) AND (t0.GROUP_CODE = t3.GROUP_CODE)) AND (t3.GROUP_CODE = 7000))) )
This generated SQL is wrong because it founds how many GroupUser have some user.
Do you have any idea where is problem?
thx.
|
|
|
|
|
|
| Re: Criteria API + countDistinct + composite primary key [message #541842 is a reply to message #541109] |
Tue, 22 June 2010 13:32  |
| Eclipse User |
|
|
|
The issue is that COUNT can only operate on a single field, so DISTINCT cannot work with composite primary keys. What EclipseLink does, is to use a EXISTS sub-selects instead of the distinct.
The SQL seems correct without the join, the join seems to be confusing things. Do you need the join, you don't seem to be using it?
The join issue seems to be a bug, please log the bug and vote for it.
As a workaround either avoid the join, or avoid the distinct, or use native SQL.
|
|
|
Powered by
FUDForum. Page generated in 0.06542 seconds
Home