When I switched from MySQL to PostgreSQL, one of the things I missed the most was the ease of paging using SQL_CALC_FOUND_ROWS and SELECT FOUND_ROWS() afterwards. One of the places I use it heavily is in my Table class, which generates, sortable, filterable and paged HTML table based on an SQL Query. The tough spot was to get all the records’ count, without supplying second query.
I found the solution in ADODB’s Pager class and it is the following:
Given the query: [SQL] (without LIMIT and OFFSET), execute the following query:
SELECT Count(*) FROM ([SQL]) AS foo;
I know it’s not as easy as SQL_CALC_FOUND_ROWS but it works;