Hello,

hello I have a function that returns the row count of a Criteria.

It basically adds a Projections.rowCount to the criterias projectionList.

It works great for me, except when the criteria already has a project.

Normally it produces the following sql:
select count(*) from cats where cats.color = ? //great!

but when I have a projections already it does the following:
select c.color, count(*) from cats c group by c.color

what I really want is:
select count(*) from (select c.color from cats c group by c.color)

I thought about using subqueries:
* create a detached criteria (DC) that would encapsulate: select c.color from cats c group by c.color
* create a criteria that does the count with DC as a sub query but couldn't figure out how to join my count(*) to DC :-)

Any other ideas?

Thanks

I'm still stuck on this problem, so here is another way to describe it.

Here is a grouping of cats by color:
c.setProjection(Projections.projectionList()
.add(Projections.groupProperty,"color"),

I want to know the size of my result set, usually I do that using the rowCount, however applying the row count to the projection list doesn't seem to work:

c.setProjection(Projections.projectionList()
.add(Projections.groupProperty,"color")
.add(Projections.rowCount));
will produce:
select color, count(*) from cats group by color

instead of (this is what I want):
select count(*) from (select color from cats group by color).

Thank you in advance.

I have exactly the same problem and I think we are not alone. Did someone managed to find some way to hedge that problem ?

Perhaps a method that returns the row count (without bypassing the potential distinct projections with rowCount projection) could be a good improvement for a future version of the Criteria package.

I describe my approach in the following thread: http://forum.hibernate.org/viewtopic.php?t=974802 (Pagination with Projection.rowCount() in a single call?).

(The fourth post from Octuber 31)