Criteria using count in a select or where clause

defiant · **Joined:** Thu May 05, 2011 4:56 am **Posts:** 3

Hello all,

I have been boggling this usse for a day almost and wondered if it is at all possible.
As a select statement it would be:

select count(something) >= 4 from bla

as a criteria with my code so far:

Code:

someNumber = 4;
codes = some list with numbers

session.createCriteria(MailLog.class).setProjection(
                            Projections.alias(Projections.count("rcptLocalpart"), "countRcpt"))
                            .add(Restrictions.ge("countRcpt", someNumber))
                            .add(Restrictions.ge("acceptedTime", beginDate))
                            .add(Restrictions.le("acceptedTime", endDate))
                            .add(Restrictions.in("bounceClass", codes)).list()

When I run the query, it returns :

Code:

could not resolve property: countRcpt of: model.MailLog;

Does anyone have any pointers for this? I have tried some variations, but I cant get my head around it.
Any help is greatly appreaciated.

Regards,
Daniel

defiant · **Joined:** Thu May 05, 2011 4:56 am **Posts:** 3

Hello all,

I was wondering if someone would have an idea how to solve this using critearia.
basically i want:

select count(somefield) > someNumber

translated into criteria.

Any help is greatly appreciated.

Cheers,
Daniel

mmerder · **Posted:** Wed May 11, 2011 9:05 am

You are looking for a having clause? It's not supported yet, but try the workaround.

defiant · **Joined:** Thu May 05, 2011 4:56 am **Posts:** 3

Okay, after many trial and error I have finally come up with the exact criteria.... but indeed it does not work :(
This is a simplified query, but this is to display my example. Also I was unable to rerieve my entire MaiLog object, so I have to do it with projections, which in the end gives you an array in return with the fields you specify in the projectionlist.

Code:

Object[] entry = session.createCriteria(MailLog.class).setProjection(
                            Projections.projectionList().add(Projections.alias(Projections.count("rcptAddress"), rcptCount")))
                           .add(Restrictions.ge("acceptedTime", beginDate))
                           .add(Restrictions.le("acceptedTime", endDate))
                           .add(Restrictions.ge("rcptCount", softBounceLimit)).list();

So this is not allowed!!
however this is(also listed in the reference, but I assumed that it was also possible for restrictions, since the reference is not exhaustive).

Code:

Object[] entry = session.createCriteria(MailLog.class).setProjection(
                            Projections.projectionList().add(Projections.alias(Projections.count("rcptAddress"), rcptCount")))
                           .add(Restrictions.ge("acceptedTime", beginDate))
                           .add(Restrictions.le("acceptedTime", endDate))
                           .addOrder(Order.asc("rcptCount")).list();

Is this going to be supported? or am i doing something wrong??

with kind regards,
Daniel

mmerder · **Posted:** Thu May 26, 2011 7:00 am

As I said, what you want is a having clause. Even in SQL it is not allowed to use group functions in a where clause:

Code:

select count(*) from MyTable where count(*) > 0

is not allowed, you have to filter using "having":

Code:

select count(*) from MyTable HAVING count(*) > 0