SelectQuery, db_query() and pager

Question

I have a long query with sub-queries using db_query().
So far I know SelectQuery of D7 comes with Extenders. Example extenders are PagerDefault (replaces Drupal 6 pager_query()).

I need my query with pager. Can I use PagerDefault with db_query()?
If I have to use db_select() for pager, how can I implement my query with sub-queries using db_select()?

$result = db_query("SELECT leg.*,
    ( SELECT COUNT(*)
    FROM {field_data_field_leg}
    WHERE field_leg_nid = leg.nid AND bundle = :bundle ) AS reviews
FROM 
    ( SELECT
        n.nid, n.created, title, field_from_value origin, field_to_value destination,
        field_price_value price, term.name transport
    FROM {node} n
        JOIN {field_data_field_leg} l ON n.nid = l.field_leg_nid
        JOIN {field_data_field_from} ff ON n.nid = ff.entity_id
        JOIN {field_data_field_to} ft ON n.nid = ft.entity_id
        JOIN {field_data_field_transport} t ON l.entity_id = t.field_transport_nid
        JOIN {field_data_field_price} p ON t.entity_id = p.entity_id
        JOIN {field_data_field_transport_type} ttype
            ON t.field_transport_nid = ttype.entity_id
        JOIN {taxonomy_term_data term} ON ttype.field_transport_type_tid = term.tid
    WHERE n.type = :type
    ORDER BY n.created DESC, field_price_value ASC
) AS leg
GROUP BY nid 
ORDER BY created DESC",
array(':bundle'=>'review', ':type' => 'leg'));

Answer 1

This should be possible.

Have a look at SelectQuery::countQuery() which is building a similar query.

Basically, instead of a table string, you can pass a full SelectQuery object to db_select().

So, you will first need to build up your inner query and then create another instance and pass the first query to it.

You might want to provide a custom count query because you can probably build one that will be much faster than having two nested subqeries.

Answer 2

The short answer is that subqueries is not possible with db_select(), and pagers are only created through the query builder, either SelectQuery or EntityFieldQuery.

So you can't the above query and utilize Drupal's pager system, you would have to manage the pager yourself if you want the above query, or rewrite the query into several queries.

Answer 3

Following up on googletorp's answer, it can't hurt to have a minimal example. (In my case I don't need to do any fancy grouping of items, so I just provide links for the next/previous page; it would be nice to see this extended to provide the "standard" grouping at the bottom of the page however!)

function mymodule_menu() {
  $items = array();

  $items['somepath'] = array(
      'title' => 'Some stuff',
      'type' => MENU_CALLBACK,
      'page callback' => 'mymodule_show',
      'page arguments' => array(1)
  );

  return $items;
}

function mymodule_get_data($page = 0){
  // change this number to suit
  $num_per_page=20;
  $offset=$num_per_page*$page;
  $results = db_query("BLA BLA ORDER BY created DESC LIMIT $offset, $num_per_page");
  $return = array();
  foreach ($results as $result) {
    $return[] = $result;
  }
  return $return;
}

function mymodule_show(){
  $page = arg(1);
  $data = mymodule_get_data($page);

  // set up your table here as usual

  $retval=theme('table', array(
                'header' => $header,
                'rows' => $rows
                 ));
  $prevpage = $page-1;
  $nextpage = $page+1;
  if($page> 0){
    $retval = $retval . l("« Newer","somepath/".$prevpage). "  ";
  }
  $retval = $retval . l("Older »","somepath/".$nextpage);
  return $retval;
}