By Anonymous (not verified) on

Hi all,

I'm running the following code:

$query = db_select('taxonomy_index', 'ti')
      ->fields('ti', array('nid'))
      ->condition('ti.tid', $term->tid)
      ->condition('n.status', 1);
    
    $query->join('node', 'n', 'n.nid = ti.nid');
    
    $query->extend('PagerDefault')->limit(2);
    
    $nids = $query->execute()->fetchCol();

but the pager does not work: every item from the query is returned, as if the call to PagerDefault is completely ignored. I am outputting theme('pager') further down in the output so that's not the problem.

This is not the only example of this failure that I have, in several other projects similar queries also bring back the full number of results every time.

I've read all the documentation, it seems to work sometimes and not other times. Anyone got any ideas?

Cheers

⋅ Categories: Drupal 7.x

Comments

kpander’s picture

kpander commented

It's a small point but it may help. I remember reading that you should identify it as a pager query right at the beginning. e.g.,

query = db_select('taxonomy_index', 'ti')->extend('PagerDefault')->limit(2);

*Then* add the fields and conditions. This helped solve the same problem for me just the other day.

Anonymous’s picture

Anonymous (not verified) commented

I can't tell you much I love you right now, thank you! :-D

Anonymous’s picture

Anonymous (not verified) commented

Actually would you believe that wasn't the problem, the issue was adding the 'join'. PagerDefault has to come before any joins.

Thanks so much for nudging me in the right direction I never would have tried that otherwise.

kpander’s picture

kpander commented

Glad to help!

For anyone else, the details about how to use PagerDefault can be found here: Database API: Extenders

kingandy’s picture

kingandy commented

I was experiencing this, and with the help of that page I realised that this was the real issue:

the extend() method will return a new object that should be used in place of the query object.

So instead of just calling $query->extend('PagerDefault'), you have to specifically set $query = $query->extend('PagerDefault')...

++Andy
Developing Drupal websites for Livelink New Media since 2008

adrupaler’s picture

adrupaler commented

this solution didn't work for me. since I used the same method for joining custom fields with node table and the pager works properly, I think something is wrong with taxonomies; in my case even the page numbers doesn't show up;


<?php
  //$query = db_select('taxonomy_index', 'ti')->extend('PagerDefault')->limit(12)
  $query = db_select('taxonomy_index', 'ti');
  $query->fields('ti', array('nid'))
        ->condition('ti.tid', (array) $taxonomy_ids, 'IN')
        ->condition('n.status', 1);
  $query->join('node', 'n', 'n.nid = ti.nid');
  $query->orderBy('n.title', 'ASC');

  $query = $query->extend('PagerDefault')->limit(12);
  $result = $query->execute();
?>