[support] Using Drupal Pager in D5 Module

[Pierre Rineau]

And ho, I forgot to escape table name in the count query, do not forget
it :)

Note: using subqueries is not advised, do it only if you have no choices
to do it, use a maximum of count,group,having etc, this is far better
for your SGBD's performances, let him rest a bit sometime :)

On jeu, 2008-08-07 at 14:40 +0200, Pierre Rineau wrote:
> 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.
>