[development] Forum module bug challenge

[Khalid Baheyeldin]

On 7/24/07, Earl Miles <merlin at 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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.drupal.org/pipermail/development/attachments/20070724/24173de5/attachment.htm