Sortable table and Pager

I've been looking at the code of the user module and the node module for quite some time trying to figure out how to get a sortable pager working, but have not yet figured it out. Just to let you know, that it is being worked on.

> The pager isn't yet working because the count query for the pager_query isn't working. You can check this by printing the global variable $pager_total after running the query:

after
$result = ppager_query('SELECT u.* FROM {users} u '. $filter['join'] .' LEFT JOIN {profile_values} pv ON u.uid = pv.uid LEFT JOIN {profile_fields} pf on pv.fid = pf.fid '. $filter['where'] .' GROUP BY u.uid '. $filter['having'] .' ORDER BY u.login DESC', $listno, 99, NULL, $filter['args']);
the lines

global $pager_total;
print_r($pager_total);

> I found out that this is due to the group by clause.

For example, this is a query that runs when I search on a location field I added with profiles.module

SELECT u.* FROM {users} u
LEFT JOIN {profile_values} pv ON u.uid = pv.uid
LEFT JOIN {profile_fields} pf on pv.fid = pf.fid
WHERE u.uid > 1 AND 1
GROUP BY u.uid HAVING SUM(IF(pf.fid = 8 AND pv.value = 'brussels',1,0)) = 1
ORDER BY u.login DESC

the count query for it, inside pager_query, will be

SELECT COUNT(*) FROM {users} u
LEFT JOIN {profile_values} pv ON u.uid = pv.uid
LEFT JOIN {profile_fields} pf on pv.fid = pf.fid
WHERE u.uid > 1 AND 1
GROUP BY u.uid HAVING SUM(IF(pf.fid = 8 AND pv.value = 'brussels',1,0)) = 1

Changing the query to

SELECT COUNT(*) FROM users u
LEFT JOIN profile_values pv ON u.uid = pv.uid
LEFT JOIN profile_fields pf on pv.fid = pf.fid
WHERE u.uid > 1
AND pf.fid = 8 AND pv.value = 'zwolle'
AND 1

seems to work for this case (returns the right count), but I don't know if the same logic will work in all other cases...

Should changing the query this way not suffice, then perhaps the query (and PHP code for it) I posted at http://drupal.org/node/107973 will do. This will allow you to also fetch values from fields added with profile.module.