I'm writing an application where users can formulate their queries on datasets (=entities) graphically, allowing them to hide columns, specify constraints and operators on them. The results are then shown in pages.

In order to be able to display something like 'Page X of Y' I need the total count of rows the query would return if it was not paginated.

This is easy for a plain query with arbitrary constraints, but not possible when one of the result set columns is a count, grouped by another column, like in this example the count of employees per department:



Ideally I would like to be able to execute a count on the (then) nested HQL or, at least, be able to obtain the generated SQL and do the count myself.

Any insights are appreciated
G.

This is a bit of a dirty solution, but you can do something along the lines of



Hope this helps,
Yair

Thank you for your reply.

I think that is not quite what I want.



would, for instance, return 100 rows, each looking like



The added select count(*) from A may return 6000, but that only leads to that the rows from my first query look like (6000, name, count-per-name).

And there is of course another problem, the count of A is not the count of rows the group by returns (in my example that would be 100, not 6000).
g.

I don't understand, you want the count of rows for that example you posted? That is, you want to return "3" ?

If that's the case you could do:
int rows=session.createQuery("select whatever").list().size();

Messy and will take a lot of memory but does the job (If I understood your question correctly, of course).

Thank you hjf for your reply!
Yes, that exactly is what I want to do. Your solution has been suggested elsewhere in the forum too, and unfortunately your assessment of it is also correct. One of the reasons for pagination is that there are many results to display, in my case prohibitively many, and query.list().size() would, as far as I grasp the mechanics of it, at least read every record (I don't think it actually would store it in memory) and thus count them by enumeration - that is too slow and causes excessive network load.

G.

OK here's your answer then. This applies to MYSQL 4 and newer I think:

suppose I have a table "test" with an ID field and a val1 field. with 6 "a" values, 4 "b" values and 4 "c values. I do:

select count(val1), val1 from test group by val1;

and i get:

6 A
4 B
4 C


I want the count of the number of rows actually, so I do:

select count(count1) from (SELECT val1, count(val1) as count1 from test group by val1) as count1;

count(count1):
3

Here's the link: http://dev.mysql.com/doc/refman/4.1/en/ ... views.html
Hope that helps!

Hello again hjf!

SQL would work, but I am writing HQL queries since I need some DB independence. The possibility of generating a SQL also occured to me - that's why I posted in my first entry the question of how to get the generated SQL from the HQL.

If that fails too then I must of course go back to SQL...
Thanx again
G.

Dear all

just writing to let you know that I found a workaround. It doesn't make me happy since it entails the application of an SQLQuery, but that seems my only option now.

In detail, the HQL which I want the row count from is almost an SQL, so only the FROM argument has to change from FROM Entity to FROM Table, the rest I can wrap around with a SELECT COUNT(*) FROM (HQL)

Thank you for your input
G.