Projections.count() and Projections.countDistinct() both result in the same query
- by Kim L
EDIT: I've edited this post completely, so that the new description of my problem includes all the details and not only what I previously considered relevant. Maybe this new description will help to solve the problem I'm facing.
I have two entity classes, Customer and CustomerGroup. The relation between customer and customer groups is ManyToMany. The customer groups are annotated in the following way in the Customer class.
@Entity
public class Customer {
...
@ManyToMany(mappedBy = "customers", fetch = FetchType.LAZY)
public Set<CustomerGroup> getCustomerGroups() {
...
}
...
public String getUuid() {
return uuid;
}
...
}
The customer reference in the customer groups class is annotated in the following way
@Entity
public class CustomerGroup {
...
@ManyToMany
public Set<Customer> getCustomers() {
...
}
...
public String getUuid() {
return uuid;
}
...
}
Note that both the CustomerGroup and Customer classes also have an UUID field. The UUID is a unique string (uniqueness is not forced in the datamodel, as you can see, it is handled as any other normal string).
What I'm trying to do, is to fetch all customers which do not belong to any customer group OR the customer group is a "valid group". The validity of a customer group is defined with a list of valid UUIDs.
I've created the following criteria query
Criteria criteria = getSession().createCriteria(Customer.class);
criteria.setProjection(Projections.countDistinct("uuid"));
criteria = criteria.createCriteria("customerGroups", "groups", Criteria.LEFT_JOIN);
List<String> uuids = getValidUUIDs();
Criterion criterion = Restrictions.isNull("groups.uuid");
if (uuids != null && uuids.size() > 0) {
criterion = Restrictions.or(criterion, Restrictions.in(
"groups.uuid", uuids));
}
criteria.add(criterion);
When executing the query, it will result in the following SQL query
select
count(*) as y0_
from
Customer this_
left outer join
CustomerGroup_Customer customergr3_
on this_.id=customergr3_.customers_id
left outer join
CustomerGroup groups1_
on customergr3_.customerGroups_id=groups1_.id
where
groups1_.uuid is null
or groups1_.uuid in (
?, ?
)
The query is exactly what I wanted, but with one exception. Since a Customer can belong to multiple CustomerGroups, left joining the CustomerGroup will result in duplicated Customer objects. Hence the count(*) will give a false value, as it only counts how many results there are. I need to get the amount of unique customers and this I expected to achieve by using the Projections.countDistinct("uuid"); -projection. For some reason, as you can see, the projection will still result in a count(*) query instead of the expected count(distinct uuid). Replacing the projection countDistinct with just count("uuid") will result in the exactly same query.
Am I doing something wrong or is this a bug?
===
"Problem" solved. Reason: PEBKAC (Problem Exists Between Keyboard And Chair). I had a branch in my code and didn't realize that the branch was executed. That branch used rowCount() instead of countDistinct().
