2

I am trying to use theme_pager to create pagination as below, but pagination does not appear. I have tried using var_dump(theme('pager', NULL, 10)) and the result is NULL.

$q="SELECT DISTINCT(node.nid) AS nid, node.type AS node_type, node.vid AS node_vid, node.title AS node_title, node.language AS node_language, node.created AS node_created, node_revisions.teaser AS node_revisions_teaser, node_revisions.format AS node_revisions_format FROM node node  LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid WHERE (node.type in ('slideshow')) AND (node.status <> 0) AND (node.language='$lang') GROUP BY nid ORDER BY node_created DESC";
$result = pager_query($q, 10, 0);
//Process data.
return $output . theme('pager', NULL, 10);

also i tried to simplify the query and to make it from one table as below, but this didn't work as well

SELECT nid, title, language, created FROM node WHERE node.type='slideshow' AND node.status  0 AND node.language='en' ORDER BY created DESC

Any idea is highly appreciated!

1
  • Have you checked that the query actually returns more results than the pager limit?
    – googletorp
    Apr 20, 2011 at 12:09

2 Answers 2

1

The problem is the query that is probably too complex, and pager_query() cannot alter it to obtain the total number of row matching the query passed as argument.
As reported in the documentation for pager_query():

The pager uses LIMIT-based queries to fetch only the records required to render a certain page. However, it has to learn the total number of records returned by the query to compute the number of pages (the number of records / records per page). This is done by inserting COUNT(*) in the original query. For example, the query SELECT nid, type FROM node WHERE status = '1' ORDER BY sticky DESC, created DESC would be rewritten to read SELECT COUNT(*) FROM node WHERE status = '1' ORDER BY sticky DESC, created DESC. Rewriting the query is accomplished using a regular expression.

Unfortunately, the rewrite rule does not always work as intended for queries that already have a COUNT(*) or a GROUP BY clause, and possibly for other complex queries. In those cases, you can optionally pass a query that will be used to count the records.

For example, if you want to page the query SELECT COUNT(*), TYPE FROM node GROUP BY TYPE, pager_query() would invoke the incorrect query SELECT COUNT(*) FROM node GROUP BY TYPE. So instead, you should pass SELECT COUNT(DISTINCT(TYPE)) FROM node as the optional $count_query parameter.

2
  • I have simplified the query to be: SELECT node.nid AS nid, node.title AS node_title, node.language AS node_language, node.created AS node_created FROM node WHERE (node.type = 'slideshow') AND (node.status <> 0) AND (node.language='en') GROUP BY nid ORDER BY node_created DESC and this didn't fix the issue
    – Alaa
    Apr 20, 2011 at 12:21
  • by removing the prefix (node.) everything works fine :D Thanks
    – Alaa
    Apr 20, 2011 at 12:36
0

Probably your $countquery is not working properly.

You can try with the following code.

pager_query($query, 15, 0, $countQuery);
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.