Problem with Drupal's pager_query()

The Drupal 6 pager_query() function has problems. If it isn't given an SQL query to count the number of items to page it rewrites the query it does have, that query returns the actual items which are being paged. Here is what it does:

$count_query = preg_replace(array('/SELECT.*?FROM /As', '/ORDER BY .*/'),
                            array('SELECT COUNT(*) FROM ', ''), $query);

All it does is replace the items being queried with COUNT(*) and remove any ordering. What it doesn't take account of is that there may be a DISTINCT in the items being requested by the query. If you install the workflow module that is exactly what happens, and suddenly the count becomes far larger than the actual number of items to be paged because some items appear twice in the results.

My solution is to change the above line to this:

$count_query = preg_replace(array('/SELECT(.*?)FROM /As', '/ORDER BY .*/'),
                            array('SELECT COUNT($1) FROM ', ''), $query);

What I have done is to preserve the original select clause from the query and wrap it in a call to COUNT(). I think this is safe as it isn't making any assumptions about what is being queried. My first attempt at fixing this was to put COUNT(n.nid) but that was making a wild assumption that the items being paged were nodes and that {node} was aliased to n.

In doing this I have commited a great Drupal sin and hacked Drupal 6 core. If anyone can tell me how to override this outside of core please let me know.

Please note: this 'solution' doesn't work, it creates invalid quaries and breaks the pager on some collections of content, so I am still without a solution to this.

Good news! I have a solution which is to use the front page view which comes as standard with the views module.

Site Section: