Block list of taxonomy related nodes

Posted by Slim Pickens on March 24, 2008 at 11:13am

Can anyone help me find out why this block code doesn't work in Drupal 6:
http://drupal.org/node/76923#comment-780887

<?php
if (arg(0) == 'node' && is_numeric(arg(1)) && is_null(arg(2))) {
  $nid = (int)arg(1);
  $terms = taxonomy_node_get_terms($nid);
  $output = "<ul>";
  foreach($terms as $term){
    $sql = "SELECT n.title, n.nid FROM {node} n INNER JOIN {term_node} tn ON n.nid = tn.nid WHERE tn.tid = $term->tid AND n.nid != $nid LIMIT 5";  
    $result = db_query(db_rewrite_sql($sql));
    if (db_num_rows($result)) {
      $output .="<li>$term->name</li><ul>";
      while ($anode = db_fetch_object($result)) {
        $output .= "<li>".l($anode->title, "node/$anode->nid")."</li>";
      }  
      $output.="</ul>";
    }
  }
  $output .= "</ul>";
  return $output;
}
?>

Cheers!

Comments

Thanks for that

Posted by Slim Pickens on March 25, 2008 at 2:23am

Thanks for that efoilia!

That works, although it brings up multiple references to the same node. I guess this would be because nodes can have multiple term tags?

There was also a missing list element in the code:

<?php if (arg(0) == 'node' && is_numeric(arg(1)) && is_null(arg(2))) { 
  $nid = (int)arg(1);
  $terms = taxonomy_node_get_terms(node_load($nid));
  foreach($terms as $term){
    $sql = "SELECT DISTINCT n.title, n.nid FROM {node} n INNER JOIN {term_node} tn ON n.nid = tn.nid WHERE tn.tid = ". $term->tid ." AND n.nid != ". $nid ." LIMIT 5";
    $sqlcount = "SELECT COUNT(DISTINCT n.nid) FROM {node} n INNER JOIN {term_node} tn ON n.nid = tn.nid WHERE tn.tid =". $term->tid ." AND n.nid !=". $nid ." LIMIT 5";
    $result = db_query(db_rewrite_sql($sql));
    $resultcount = db_result(db_query(db_rewrite_sql($sqlcount)));
    if ($resultcount) {
      $output .= '<li>'. l($term->name,"taxonomy/term/$term->tid") .'<ul>';
      while ($anode = db_fetch_object($result)) {
      $output .= '<li>'. l($anode->title, "node/$anode->nid") .'</li>';
        }
      $output .= "</ul>";
      }
    }
    return $output;
    }?>

I've played with this some

Posted by Slim Pickens on March 25, 2008 at 3:20am

I've played with this some more and come up with this solution:

<?php if (arg(0) == 'node' && is_numeric(arg(1)) && is_null(arg(2))) {
  $num_nodes = 5; 
  $nid = (int)arg(1);
  $terms = taxonomy_node_get_terms(node_load($nid));
  foreach($terms as $term){
    $sql = "SELECT DISTINCT n.title, n.nid FROM {node} n INNER JOIN {term_node} tn ON n.nid = tn.nid WHERE n.status = 1 AND tn.tid = ". $term->tid ." AND n.nid != ". $nid ." ORDER BY n.created DESC LIMIT $num_nodes";
    $sqlcount = "SELECT COUNT(DISTINCT n.nid) FROM {node} n INNER JOIN {term_node} tn ON n.nid = tn.nid WHERE tn.tid =". $term->tid ." AND n.nid !=". $nid ." LIMIT $num_nodes";
    $result = db_query(db_rewrite_sql($sql));
    $resultcount = db_result(db_query(db_rewrite_sql($sqlcount)));
    if ($resultcount) {
      while ($anode = db_fetch_object($result)) {
      $output = node_title_list($result);
        }
      }
    }
    return $output;
    }?>

The node_title_list function passes the output to the theme formatting.

$num_nodes to set LIMIT

I presume that this line:
$output .= '<li>'. l($term->name,"taxonomy/term/$term->tid") .'<ul>';
was meant to produce a list of terms, but it isn't so I've removed it.

I added ORDER BY n.created DESC to sort by creation date.

You can see it here: http://www.geelongfolkmusicclub.com/nick-charles-irish-murphys

Nice

Posted by efolia on March 25, 2008 at 3:36am

That's a nice application of Drupal (and of that particular piece of code). Indeed, separating node titles with the actual term names was a bit of an overkill, given the very limited scope of the listing (5 in this case).

Have fun and good luck :-)
efolia

efolia

This is awesome . . . only

Posted by greggory.hz on April 29, 2008 at 11:47pm

This is awesome . . . only trouble is, i have two taxonomy terms in each of the nodes. I want this to relate based on one of the fields but not the other. For example, I have type and category. Right now, that code groups by type and I want it to group by category. How can I accomplish that??

This worked well. Thanks.

Posted by Rob T on May 16, 2008 at 5:06am

This worked well. Thanks.

Yet another bug, first result missing!

Posted by zany on May 20, 2008 at 2:32am

Actually node_title_list enumerates the result on it's own. This way the db_fetch_object call will discard the first result! In other words use this code:

<?php if (arg(0) == 'node' && is_numeric(arg(1)) && is_null(arg(2))) {
  $num_nodes = 5; 
  $nid = (int)arg(1);
  $terms = taxonomy_node_get_terms(node_load($nid));
  foreach($terms as $term){
    $sql = "SELECT DISTINCT n.title, n.nid FROM {node} n INNER JOIN {term_node} tn ON n.nid = tn.nid WHERE n.status = 1 AND tn.tid = ". $term->tid ." AND n.nid != ". $nid ." ORDER BY n.created DESC LIMIT $num_nodes";
    $result = db_query(db_rewrite_sql($sql));
    $output = node_title_list($result);
  }
  return $output;
}?>

This also eliminates the second SQL call. node_title_list can handle empty results well on it's own...

Use something like the code below if you want the block to show even when there are no tags.

return $output ? $output : "No links";

This lists up to $num_nodes links from one term. I.e. some kind of category. If you need multiple terms unroll the code from http://api.drupal.org/api/function/node_title_list/6 into this.

Thanks zany. That works for

Posted by Slim Pickens on May 20, 2008 at 4:47am

Thanks zany. That works for me and it's obviously more efficient too!

The original block of code

Posted by waltercat on May 22, 2008 at 1:11am

The original block of code doesn't seem to be working for drupal 5 either. I get sql errors. How would this code be altered to work for 5?

If you can access your

Posted by Slim Pickens on May 23, 2008 at 7:46am

If you can access your database through PHPMyAdmin for example, you can test the SQL query there until you find what's wrong. The exact syntax can depend on the version of mySQL being run on your host

You might try removing the curly braces eg {node} - I have found that to help, I seem to recall.

Good stuff. Thanks for the

Posted by Rob T on May 27, 2008 at 3:20pm

Good stuff. Thanks for the snippet.

postgres

Posted by alek123 on April 7, 2009 at 10:20pm

Wonderfull!, Only one thing, in a Postgres install we need to put the ORDER BY field y the SELECT DISTINCT list in order to avoid Warnings. Here it is with this little change.

 <?php if (arg(0) == 'node' && is_numeric(arg(1)) && is_null(arg(2))) {
  $num_nodes = 5;
  $nid = (int)arg(1);
  $terms = taxonomy_node_get_terms(node_load($nid));
  foreach($terms as $term){
    $sql = "SELECT DISTINCT n.title, n.nid, n.created FROM {node} n INNER JOIN {term_node} tn ON n.nid = tn.nid WHERE n.status = 1 AND tn.tid = ". $term->tid ." AND n.nid != ". $nid ." ORDER BY n.created DESC LIMIT $num_nodes";
    $result = db_query(db_rewrite_sql($sql));
    $output = node_title_list($result);
  }
  return $output;
}?>

Thanks!

Alek

El universo no se creo de la noche a la mañana...

Nice code - can it be used with Views 2 queries?

Posted by lhugg on May 13, 2009 at 4:25am

Is there a way to use part of this to extract the taxonomy associated with a node in the argument portion of a Views 2 query?

Changes to Drupal 6 core?

Posted by CtrlAltDelete on August 14, 2009 at 5:42am

None of the above snippets seem to work with my D6 (ver 6.13 and mySQL) installation. Were changes made to the Drupal core files after these snippets where posted that breaks them? I know that the code for the link "l" function is incorrect but can't figure out what else is wrong. Any input?

do the same without node_load

Posted by tetramentis on October 18, 2009 at 1:03pm

Today I needed to get node's terms by its nid, but without loading the node (for the sake of efficiency).

One may trick the taxonomy_node_get_terms() function by replacing code

  $nid = (int)arg(1);
  $terms = taxonomy_node_get_terms(node_load($nid));

with

  $nid = (int)arg(1);
  $node = new stdClass;
  $node->vid = $nid;
  $terms = taxonomy_node_get_terms($node);

This code assumes that you are not using node revisions (which is the case for all Drupal sites I've created to date).

Enjoy!

-----
http://bogdan.org.ua/

Is it possible to make the

Posted by JubW on August 15, 2008 at 6:30pm

Is it possible to make the order random and not by date?

Web Hosting Coupons

ORDER BY RAND() if you need random ordering

Posted by zany on November 23, 2008 at 12:52am

Jub,

you got to change the ORDER BY clause to e.g. ORDER BY RAND() if you need random ordering.

Another version

Posted by zigazou on July 13, 2010 at 9:40am

Here’s my version, inspired by the post of tetramentis about the node_load trick :

It does not use any foreach loop thus generating only one SQL query (apart the one implied by taxonomy_node_get_terms).
It limits the search by vocabulary.
It sorts the results by the most relevant nodes (the ones having the most terms in common with the current node).

<?php
  if(arg(0)=='node' && is_numeric(arg(1)) && is_null(arg(2))) {
    $num_nodes=5;
    $nid      =(int)arg(1);
    $vid      =1;
    $node     =new stdClass;
    $node->vid=$nid;
    $terms    =taxonomy_node_get_terms_by_vocabulary($node,$vid);
    $terms    =implode(',',array_keys($terms));

    $sql="
      SELECT     COUNT(n.nid), n.nid, n.title
      FROM       {node} n
      INNER JOIN {term_node} tn
      ON         n.nid=tn.nid
      WHERE      n.status=1
      AND        tn.tid IN ($terms)
      AND        n.nid!=$nid
      GROUP BY   n.nid
      ORDER BY   COUNT(n.nid) DESC, n.changed DESC
      LIMIT      $num_nodes
    ";

    $result=db_query(db_rewrite_sql($sql));
    $output=node_list_title($result);
  
    return $output;
  }
?>

Great post, but typo

Posted by seanwallis on September 15, 2010 at 11:31am

This is the proper way to solve this problem, i.e. obtain the closest match between nodes by the taxonomy, aka 'taxonomy terms in common'.

NB. There is a typo which stumped me for a while. I think this should be node_title_list rather than node_list_title here!

Plus in my version I check whether $terms is not null prior to launching the SQL query, i.e. brace the code with if ($terms) { ... } just prior to calling implode.

Excluding the current book

Posted by seanwallis on September 21, 2010 at 7:40am

I have used this excellent snippet to create a 'See also' block which does not include elements in the current book (if there is one).

The current book is displayed in another block using the menu_tree_output(menu_tree_all_data($node->book['menu_name'])); method. See http://drupal.org/node/44648.

To exclude all items belonging to the current book in SQL we first need to gather nids from the current {book} using a SELECT clause and then perform a LEFT OUTER JOIN with a WHERE IS NULL condition. This code creates a single SQL query in two flavours, one where the page is part of a book and one where it is not.

For safety I load the node below.

Sean

<?php

if (arg(0) == 'node' && is_numeric(arg(1)) && is_null(arg(2))) 
{
  $num_nodes = 10; 
  $nid = (int)arg(1);
  $node = node_load($nid);
  $terms = taxonomy_node_get_terms($node);
  if ($terms)
  {
    $terms = implode(',',array_keys($terms));
    $sql = "SELECT COUNT(n.nid), n.nid, n.title
        FROM  {node} n
        INNER JOIN {term_node} tn
        ON    n.nid=tn.nid ";

    if ($node->book)
    {
      $bid = $node->book['bid'];
      $sql .= "LEFT OUTER JOIN 
           ( SELECT b.nid
             FROM {book} b 
             WHERE b.bid = $bid 
           ) AS b2
           ON tn.nid = b2.nid 
           WHERE b2.nid IS NULL 
           AND ";
    }
    else
      $sql .= "WHERE ";
     
    $sql.= "n.status = 1
        AND tn.tid IN ($terms) ";

    if (!$node->book)
      $sql .= "AND n.nid != $nid ";

    $sql.= "GROUP BY n.nid
        ORDER BY COUNT(n.nid) DESC, n.title ASC
        LIMIT $num_nodes";

    $result = db_query(db_rewrite_sql($sql));
    $output = node_title_list($result);
  }
  return $output;
}
?>

Here is my version of getting related nodes

Posted by pwaterz on July 20, 2010 at 3:53pm

This method searches all vocabulary groups. And also only makes one SQL call.

<?php
function get_related_nodes($nid, $num_nodes){
  
  $terms = taxonomy_node_get_terms(node_load($nid));
  $sql = "SELECT DISTINCT node.title, node.nid, node.title FROM node, term_node where (node.nid = term_node.nid) and (node.status = 1) AND (";
  $count = 0;    

  foreach($terms as $term){
    if($count == 0){    
        $sql = $sql . "(term_node.tid = " .$term->tid." )"; 
   } else {
        $sql = $sql . " OR (term_node.tid = " .$term->tid." )"; 
   }
   $count++;
  }
  
       $end = ") AND (node.nid != $nid) ORDER BY node.created DESC LIMIT $num_nodes";
    $sql = $sql .$end;
    
    
    $result = db_query($sql);
    $output = node_title_list($result);
    
    return $output;

}
?>

PJW

typo in API function

Posted by CtrlAltDelete on September 8, 2010 at 8:49pm

The correct function in the snippet should be "node_list_title" so the actual code would be $output = node_list_title($result);

No, this is an error

Posted by seanwallis on September 21, 2010 at 6:57am

Always check the API: http://api.drupal.org/api/function/node_title_list/6

Can't the same thing be

Posted by skolesnyk on September 16, 2010 at 2:19pm

Can't the same thing be achived with views relationships and arguments? I think you can! )

great code

Posted by anjjriit on August 29, 2010 at 4:34pm

You save my life with your code, thanks a ton, how to add filter so i can displayed list a of related content only for posted node before the node is created, like illustrated below

node/1, posted date 2010-08-02, term 1
node/2, posted date 2010-08-03, term 2
node/3, posted date 2010-08-04, term 1
node/4, posted date 2010-08-06, term 1
node/5, posted date 2010-08-06, term 2
node/6, posted date 2010-08-07, term 1

so if node/4 is active
list of related content only contain
node/1, and
node/3
because node/1 and node/3 was posted before node/4.

node/6 is not include in list because it's posted after node/4
Please help my one more time

Hi, Can anyone tell me how I

Posted by Summit on February 12, 2011 at 12:47pm

Hi,
Can anyone tell me how I can alter this script, so it shows not only the most related, but also the most popular nodes?
Thanks a lot in advance for your reply!
greetings, Martijn