By enes-1 on

I found this code for displaying related nodes to the current node by taxonomy term http://drupal.org/node/76923
and it works great but I have one problem, I have nodes related to many vocabularies and I need to display nodes related to only one specific vocabulary. I'm pretty much new to php and drupal.

⋅ Categories: Drupal 5.x

Comments

jastraat’s picture

jastraat commented

You could modify the SQL statement in the post you referenced and add a where clause for vocabulary id (vid). Just set it equal to the id of the vocabulary you want to search.

------------------------------
http://fraggles.artsci.wustl.edu (Drupal user documentation and development blog)

enes-1’s picture

enes-1 commented

Thanx for reply Jastraat I know I need to do something like that but hoooow :-) I've been fighting with this code for 3 hours now :-)

jastraat’s picture

jastraat commented

So - the original SQL looks like this:

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

To add in a clause for vid, I believe you could simply modify it to the following (example using a vocabulary id of 2 - replace with the id of the one you wish to pull from):

$vid = 2;
SELECT n.title, n.nid FROM {node} n INNER JOIN {term_node} tn ON n.nid = tn.nid WHERE tn.tid = $term->tid AND tn.tid IN (SELECT tid from term_data WHERE vid = $vid) AND n.nid != $nid LIMIT 5

I'm sure there's a more efficient way to do this than with a subquery, but I don't have access to view the database schema for my Drupal site from home. Hope that provides the result you were looking for!

------------------------------
http://fraggles.artsci.wustl.edu (Drupal user documentation and development blog)

enes-1’s picture

enes-1 commented

Great, thanx Jastraat, I had problem with selecting from term_data but that was because I had table prefix, when I ad this prefix everything works as it should, thanx again
if you find that other more efficient way around this problem please share it, but this works fine for me :-)
also do you think this can be achieved with views, that way you can include thumbnail of the specific node?

jastraat’s picture

jastraat commented

It's probably possible to do this with a view + arguments, but I'm not exactly sure how to go about it. It would be easy enough to add a vocabulary filter. The tricky part would be passing in the terms of a node as an argument. It might work best to just pass in the node id as an argument and then use some argument handling code to get the terms for that node, add a filter for nodes that have the same terms.

Alternatively, you could probably alter that block to display a thumbnail instead of a link. It's just a matter of getting different values from the database and then setting the $output variable to display a thumbnail instead of a list item.

------------------------------
http://fraggles.artsci.wustl.edu (Drupal user documentation and development blog)

solutionsphp’s picture

solutionsphp commented

Using the code found here:

http://drupal.org/node/76923

I get a SQL error. Drupal 5.2.

user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT(n.nid) FROM node n INNER JOIN term_node tn ON n.nid = t query: SELECT n.title, DISTINCT(n.nid) FROM node n INNER JOIN term_node tn ON n.nid = tn.nid LEFT JOIN i18n_node i18n ON n.nid = i18n.nid WHERE (i18n.language ='en' OR i18n.language ='' OR i18n.language IS NULL) AND ( tn.tid = 28 AND n.nid != 11 ) LIMIT 5

Also, how would the SQL query be modified to pull related nodes from a particular TERM?

TIA!

My review of David Mercer's "Drupal: Creating Blogs, Forums, Portals and Community Websites"

jastraat’s picture

jastraat commented

You could put this into a block to show all the nodes associated with a particular term. This would be completely independent of the node in which the block rested however - so not pulling a list of nodes with the same term as the current node. This also doesn't limit the results returned. It would probably be much much easier to do this with a block view using the views module.

    //Set the id for the term for which you want to return associated nodes
    $block_term = 1;
    $output = "<ul>";
    $sql = "SELECT n.title, n.nid FROM {node} n INNER JOIN {term_node} tn ON n.nid = tn.nid WHERE tn.tid = $block_term";  
    $result = db_query(db_rewrite_sql($sql));
    if (db_num_rows($result)) {
      while ($anode = db_fetch_object($result)) {
        $output .= "<li>".l($anode->title, "node/$anode->nid")."</li>";
      }  
    $output .= "</ul>";
    return $output;
}

------------------------------
http://fraggles.artsci.wustl.edu (Drupal user documentation and development blog)