[development] Forum module bug challenge

[Sean Robertson]

Ooooh - that's REALLY useful to me.  How would you take that one step 
further and limit it to the current category?



Khalid Baheyeldin wrote:
> On 7/24/07, *Earl Miles* <merlin at logrus.com <mailto: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>
> http://2bits.com
> Drupal development, customization and consulting.

-- 
Sean Robertson
Web Developer
NGP Software, Inc.
seanr at ngpsoftware.com
(202) 686-9330
http://www.ngpsoftware.com