[support] Using Drupal Pager in D5 Module

[Pierre Rineau]

Ok; I would not have written you request the same, try:

$sql = "SELECT nid, title, teaser FROM {node_revisions} WHERE body like '%%s%' GROUP BY nid ORDER BY nid";
$sql_count = SELECT COUNT(DISTINCT(nid)) FROM node_revisions WHERE body like '%%s%'";

I dont really like the use of DISTINCT, but it works here, do a count(nid) should also work too, but I cant remember the exact syntax.
May help (pager_query rewrite himself request if you don't give him a count query, so with group clauses you should provide your own count query).
Try to see with my first mail if you are calling correctly the theme_pager too.

PS: This should go back to drupal support list, I CC this mail there, please respond to the list.

On jeu, 2008-08-07 at 13:04 +0200, Fred Jones wrote:
> > May be your call to theme_pager is not good.
> 
> >>       $sql = "SELECT nid, title, teaser FROM {node_revisions} WHERE body
> >> like '%%%s%' ".
> >>                       'group by nid order by nid';
> 
> Was the group by clause here. That must be removed in the $count_query
> and the $count_query must be defined manually and submitted to
> pager_query. But the truth is that just removing that group by clause
> will give incorrect results, because now the count_query may find more
> results than the actual search query. So I am using this SQL:
> 
> 	$countsql = "SELECT count(*) FROM (SELECT nid from {node_revisions}
> WHERE body like '%%%s%' group by nid) as temp_table_mymodule_search ";
> 
> which does the search, grouped by nid, as a sub-select, thus the
> overall select gives the correct result.
> 
> Thanks for your help--I poked around in the pager.inc source code
> until I figured out what exactly the problem was.