By akamarvin on

this was maybe already pointed out .

If you build a table with a pager based on a sql query in which you use the distinct keyword, Be sure to adapt your call to the pager_query() function in consequence.

you have to specify the fourth parameter of this function : $count_query. If you don't, the system will try with his own count query which is just a replacement of your select by a count(*). Which will get you more record count than you actually have with you query using distinct.

I was pulling on my hair for hours, becoming mad , not understanding why I had so many empty pages in the pager navigation colspan and then I found out in pager.inc this tricky count(*) replacement and there was light.

By providing your own count query (basicallly "select count( DISTINCT(your_field) ) from ... where ..." ) you solve the problem and get the whole page count right. I just tought I'd share that with you as it was pain for me to find out the solution.

Comments

akamarvin’s picture

akamarvin commented

the module makes great use of patchs you have to apply almost everywhere.

and uses queries with the distinct keyword .

Again, it messes up the pager if no adequate count query is provided.

[Problem submitted as a bug to the term access project.]

[aka]marvin

ndmaque’s picture

ndmaque commented

8 years later and still an excellent post, i realised i had to add a count query but after frustration had got the distinct / count nesting the wrong way round!
I found the upper case helped.

In case it helps anyone my solution for searching certain profile fields that contain a string.
We didn't expect many results so could afford to add a user_load during the while loop.

function intag_profile_search($text) {
 global $pager_page_array, $pager_total, $pager_total_items;
  $text = check_plain(strtoupper($text)) ; 
  $rows = array() ; 
  #fid: 15=invoice address 16=delivery address 10=business name 14=phone
  
  $text = check_plain(strtoupper($vals['text'])) ; 
  $sql = "SELECT DISTINCT uid FROM {profile_values} WHERE  fid IN (15,16,14,10)  AND (value LIKE '%%%s%%') ORDER BY uid ASC" ; 
  $count_sql = "SELECT COUNT(DISTINCT(uid)) FROM {profile_values} WHERE fid IN (15,16,14,10) AND (value LIKE '%%%s%%') ";
  
  $res = pager_query($sql, 10, 0, $count_sql, $text);
  $pager = theme('pager', NULL, 10, 0);
  
  while($row = db_fetch_object($res)) {
     $user = user_load($row->uid) ; 
     $rows[] = array( $user->name , $user->profile_bus_name, $user->profile_phone,  $user->profile_add ); 
  }

  $header = array('Edit', 'Business Name', 'Phone', 'Address');
  $result = theme_table($header, $rows);
  
  return 'Found '. $pager_total_items[0] . " users with $text." . $result  . $pager ; 

}

Thanks