Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Criteria API + countDistinct + composite primary key
Criteria API + countDistinct + composite primary key [message #540813] Thu, 17 June 2010 08:39 Go to next message
Radek Hodain is currently offline Radek Hodain
Messages: 16
Registered: February 2010
Junior Member
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 #540917 is a reply to message #540813] Thu, 17 June 2010 11:38 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

You SQL seems to be for a much more complex query, could you please include the code for the query, and the full SQL. Your SQL also starts with FROM??

Why do you need to use a distinct, does it work correctly if you remove the distinct?



James : Wiki : Book : Blog : Twitter
Re: Criteria API + countDistinct + composite primary key [message #541109 is a reply to message #540917] Fri, 18 June 2010 07:54 Go to previous messageGo to next message
Radek Hodain is currently offline Radek Hodain
Messages: 16
Registered: February 2010
Junior Member
Hi,

here is my code:

CriteriaBuilder b = manager.getCriteriaBuilder();
CriteriaQuery<Long> q = b.createQuery(Long.class);
Root<UserGroupAsoc> identificationVariable = q.from(UserGroupAsoc.class);
identificationVariable.join("group", JoinType.LEFT);
q.select(b.countDistinct(identificationVariable));
q.where(b.equal(identificationVariable.get("user"), user));
TypedQuery<Long> createQuery = manager.createQuery(q);
Long count = createQuery.getSingleResult();

SQL:

SELECT COUNT(t0.user_code) FROM GROUPUSER t2 LEFT OUTER JOIN GROUP t1 ON (t1.group_code = t2.group_code), GROUPUSER t0 WHERE EXISTS (SELECT t2.user_code FROM GROUPUSER t2 WHERE (((t0.user_code = t2.user_code) AND (t0.group_code = t2.group_code)) AND (t2.user_code = 1)))

If I use the method count, it works correct.

I tried to remove identificationVariable.join("group", JoinType.LEFT); and the result was similar with EXISTS part but the result of the SQL command was correct.

SQL without join:
SELECT COUNT(t0.user_code) FROM GROUPUSER t0 WHERE EXISTS (SELECT t1.user_code FROM GROUPUSER t1 WHERE (((t0.user_code = t1.user_code) AND (t0.group_code = t1.group_code)) AND (t1.user_code = 1))) .

We need distinct because we use left outer joins.


Re: Criteria API + countDistinct + composite primary key [message #541732 is a reply to message #541109] Tue, 22 June 2010 07:12 Go to previous messageGo to next message
Radek Hodain is currently offline Radek Hodain
Messages: 16
Registered: February 2010
Junior Member
I haven't written yet that I use eclipselink the version 2.1.0-RC4.2 with Oracle database.

RH
Re: Criteria API + countDistinct + composite primary key [message #541842 is a reply to message #541109] Tue, 22 June 2010 13:32 Go to previous message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

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.


James : Wiki : Book : Blog : Twitter
Previous Topic:EclipseLink, JTA, GlassFish Embedded - No transaction is currently active exception
Next Topic:IN_OUT Parameters not working for NamedStoredProcedureQuery
Goto Forum:
  


Current Time: Thu Apr 17 23:39:15 EDT 2014

Powered by FUDForum. Page generated in 0.02185 seconds