db_select with Pager and range

By leeresglas on 14 May 2014 at 07:49 UTC

Hi,

in a project I'm currently working on, I want to use a db_select query with a pager and range. However I had no success achieving it.

My code is the following:

      $query = db_select('node', 'n');
      $query = $query->extend('PagerDefault')->element('0')->limit($limit);
      $query->fields('n', array('nid', 'title'))
        ->condition('n.type', 'project');
      $query->addTag('node_access');
      $query->groupBy('n.nid');
      $query->range(0,3);
      $results = $query->execute()->fetchAll();

The range values are ignored in this setting.

I presume the reason for this behavior can be found in https://api.drupal.org/api/drupal/includes!pager.inc/function/PagerDefau... , as the pager execution function overwrites the range function of the pager query there. I don't see a reason to not include original range values as offset / max values there, but anyway, is there a way to achieve pagination AND offset in a db_select query?

Best,
Alex

Comments

I believe the following is

Jaypan commented 16 May 2014 at 11:41

I believe the following is what you want.

      $query = db_select('node', 'n')->extend('PagerDefault');
      $query->fields('n', array('nid', 'title'))
        ->limit($limit)
        ->condition('n.type', 'project')
        ->addTag('node_access')
        ->groupBy('n.nid');
      $results = $query->execute()->fetchAll();

The start of your range is defined by the page set in $_GET['page']. The number of values is determined by the limit().

Hm, no, not really. Suppose I

leeresglas commented 16 May 2014 at 12:09

Hm, no, not really. Suppose I have 40 nodes that are displayed on a page of 10 items each. What I want, is to exclude the first 2 items from this list of nodes. In the end I've chosen to use two queries where the second query (which is the paged one) is offset by a condition that relates to the ordering values of the first query.

Views?

RoloDMonkey commented 16 May 2014 at 12:58

Why not use a View? I'm pretty sure this is possible with the Views module.

Because of multiple filters

leeresglas commented 16 May 2014 at 14:17

Because of multiple filters and joins and expressions that would be (at least in my opinion) much more difficult to implement in views...

That's legit.

RoloDMonkey commented 16 May 2014 at 14:49

I have some incredibly complicated queries using Views and Views-related modules. But, sometimes that is more cumbersome than writing the whole thing myself. Also, Views can generate some remarkably inefficient queries.

What I would do is find the settings in Views that allow you to determine an initial off-set, and then follow the code to see how those settings effect the query building. However, this might be quite a bit of work since Views are so abstracted that it might be hard to trace the setting all the way back to the query.