as an update to this thread: as of EE 1.6.8 build 20091201, the code from my last post to this thread is now incorporated into EE so it is not necessary to maintain this hack (or fear upgrading to preserve it).
/happy
================ORIGINAL POST===================
The line we’ve heard time and time again (<- 4 separate links) is that the query module doesn’t support pagination if you have subqueries or grouping in your SQL statement.
As I have mentioned before, it seems to be this way because EE runs the query twice if you’re paginating results, except it replaces the SELECT fields with COUNT(*) to get the total number of results so it knows how many links to create. I have also mentioned before that this is asinine because the num_rows variable exists whenever you run a query through the $DB->query function.
I have finally butted heads with this issue for the last time, and am putting my money where my mouth is. I propose the following changes as a hack/fix (feel free to incorporate this into the next version, if it passes muster) to this limitation:
mod.query.php:
line 97:
// Edit by Ty Wangsness, allows pagination to work with more complex queries
// $query = $DB->query(preg_replace("/SELECT(.*?)\s+FROM\s+/is", 'SELECT COUNT(*) AS count FROM ', $sql));
$query = $DB->query($sql);
line 99:
// if ($query->row['count'] == 0)
if($query->num_rows == 0)
line 117:
// $this->total_rows = $query->row['count'];
$this->total_rows = $query->num_rows;
It appears to work as expected in my limited tests (read: it works for me, YMMV). Hopefully we can put this issue to rest.
-Ty
