Block list of taxonomy related nodes

By Slim Pickens on 24 Mar 2008 at 11:13 UTC

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

Slim Pickens commented 25 March 2008 at 02:23

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

Slim Pickens commented 25 March 2008 at 03:20

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

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

This is awesome . . . only

greggory.hz commented 29 April 2008 at 23:47

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.

Rob T commented 16 May 2008 at 05:06

This worked well. Thanks.

Yet another bug, first result missing!

zany commented 20 May 2008 at 02:32

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

Slim Pickens commented 20 May 2008 at 04:47

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

The original block of code

waltercat commented 22 May 2008 at 01:11

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

Slim Pickens commented 23 May 2008 at 07:46

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

Rob T commented 27 May 2008 at 15:20

Good stuff. Thanks for the snippet.

postgres

alek123 commented 7 April 2009 at 22:20

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

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

lhugg commented 13 May 2009 at 04:25

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?

CtrlAltDelete commented 14 August 2009 at 05:42

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

tetramentis commented 18 October 2009 at 13:03

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!

Is it possible to make the

JubW commented 15 August 2008 at 18:30

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

ORDER BY RAND() if you need random ordering

zany commented 23 November 2008 at 00:52

Jub,

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

Another version

Anonymous (not verified) commented 13 July 2010 at 09:40

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).

  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

seanwallis commented 15 September 2010 at 11:31

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

seanwallis commented 21 September 2010 at 07:40

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

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

pwaterz commented 20 July 2010 at 15:53

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

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

CtrlAltDelete commented 8 September 2010 at 20:49

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

seanwallis commented 21 September 2010 at 06:57

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

Can't the same thing be

skolesnyk commented 16 September 2010 at 14:19

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

great code

anjjriit commented 29 August 2010 at 16:34

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

summit commented 12 February 2011 at 12:47

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

Block list of taxonomy related nodes

Comments

A few reasons

Thanks for that

I've played with this some

Nice

This is awesome . . . only

This worked well. Thanks.

Yet another bug, first result missing!

Thanks zany. That works for

The original block of code

If you can access your

Good stuff. Thanks for the

postgres

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

Changes to Drupal 6 core?

do the same without node_load

Is it possible to make the

ORDER BY RAND() if you need random ordering

Another version

Great post, but typo

Excluding the current book

Here is my version of getting related nodes

typo in API function

No, this is an error

Can't the same thing be

great code

Hi, Can anyone tell me how I

New forum topics

News items

Our community

Documentation

Drupal code base

Governance of community