thoughts on "count queries"

[Robert Zeigler]

I've been working on the tapestry/cayenne integration.  One thing that  
would be nice is to have automatic "conversion" from query to  
"GridDataSource" (the model backing tapestry's Grid component), so  
that you could simply define a query and pass that to the grid to have  
the results displayed, paginated, etc.

I have a first pass of this working by simply executing the query to  
fetch the list of objects and allowing tapestry to convert the list  
into a GridDataSource.  This works... for small lists. But certainly  
won't scale for anything large.  GridDataSource provides the hooks  
required to select a "page" of data at a time.  And queries now allow  
for setting fetch limits and offsets, which allows me to generically  
program this to handle many (most?) common use-cases.  So, I'm 90%  
there.

But there's still one thing lacking.  The GridDataSource has to tell  
tapestry how many rows are available, total.

For the many cases* (those that don't involve row aggregation), this  
can be accomplished via a simple count statement; ie, executing / 
nearly/ the same query... but selecting the count of the records  
rather than the actual properties.

So I got to thinking that maybe there would be a way to take an  
existing query and "tweak" it to perform a counting version of its  
query.  Thoughts on this approach?


* This breaks down if the query is doing any kind of grouping.  I'm  
not aware of a standard way to ask how many rows a particular query / 
would/ in this case. Anybody? On mysql, one could set the fetch limit  
to 1, perform the query, and then perform a query for "found_rows()",  
but that's not applicable anywhere but mysql, so...

Robert