#2 Row count with "limit"

open
nobody
None
5
2009-06-18
2009-06-18
Marius Ruseckas
No

When using paging we define 'limit' and 'offset' but also we need to know how many records query could return without limitation conditions.
(Example: showing records 50-100 of 874)
So, now we need issue 2 queries 1 with limit condition to obtain records and another one without - to obtain total records count.
Suggestion:
Update method
public ResultsIterator select(final Class beanClass, Map propertyValues,
String orderByProperty, boolean descending, Integer limit, Integer offset,
BeanFactory beanFactory) throws StoreException

with following:
String queryString = "SELECT " + (limit == null ? "" : "SQL_CALC_FOUND_ROWS " ) + "* FROM " + normalTableName
+ (cond == null ? "" : " WHERE " + cond) + (normalOrderBy == null ? "" : " ORDER BY " + normalOrderBy)
+ (descending ? " DESC " : "") + (limit == null ? "" : " LIMIT " + limit)
+ (offset == null ? "" : " OFFSET " + offset);

SQL_CALC_FOUND_ROWS - is transparent (does not effects results), it only stores internally data for following query

Create new method
public Integer found_rows() throws StoreException

witch executes following sql statement "SELECT FOUND_ROWS()"

Discussion