On 7/24/07, Earl Miles <merlin@logrus.com> wrote:

This can be much more efficiently done in two queries where you do a limit 1
and do > on one and < on the other, using the sort criteria, to determine what
next/previous is.

For example, if you're sorting on created, you do a SELECT nid FROM node WHERE
[sufficient clauses to restrict to just this forum] WHERE node.created >=
$node->created AND node.nid <> $node->nid LIMIT 1

And reverse it to find the previous.

I did something similar for a client. They wanted to display the thumbnails of
the next/previous image below each image node.

What I did is go by the node ID.

So this function does what you mentioned above.

function pn_node($node, $mode) {
switch($mode) {
    case 'p':
      $op = '<';
      $order = 'DESC';
      break;

    case 'n':
      $op = '>';
      $order = 'ASC';
      break;

    default:
      return NULL;
}

$sql = "SELECT n.nid FROM {node} n
    WHERE n.nid $op %d
    AND n.type in ('image', 'video')
    AND n.status = 1
    AND n.promote = 1
    ORDER BY n.nid $order
    LIMIT 1";

$n_nid = db_result(db_query($sql, $node->nid));
if ($n_nid) {
    $n_node = node_load($n_nid);
    if ($n_node->type == 'image') {
        return l(image_display($n_node, 'thumbnail'), "node/$n_nid", array(), NULL, NULL, FALSE, TRUE);
    }
}
}

Then in the node-image.tpl.php file, I have this:

<?php
if ($page) {
print pn_node($node, 'n');
print pn_node($node, 'p');
}
?>

It is not a bottleneck as far as queries go, but it does get executed a lot.

The issue with forums is that you have to join the term_node too and filter by
the forum, which will add to the query time.
--
2bits.com
http://2bits.com
Drupal development, customization and consulting.