By Cosmos-1 on

how would i edit this to show only nodes of taxonomy term 1?

This is from nodevote

$sql = db_rewrite_sql('SELECT n.nid, n.title, AVG(vote) AS av_avg FROM {node} n, {nodevote} nv WHERE n.nid = nv.nid GROUP BY n.nid ORDER BY av_avg DESC');

i know i need to add n.tid = 1 or something.. somewhere. but whenever i try i get errors. ive tried all kinds of combinations .. i cant guess how to do it.

⋅ Categories: Drupal 4.6.x

Comments

gollyg’s picture

gollyg commented

It is actually the sql you are needing to edit. The problem is that tid is not a field in the node table - it is in the term_node table. So you need to select the term_node table and join it based on the nid. Add these to an AND statement something like the following

$sql = db_rewrite_sql('SELECT n.nid, n.title, AVG(vote) AS av_avg 
FROM {node} n, {nodevote} nv, term_node 
WHERE n.nid = nv.nid 
AND n.nid = term_node.nid
AND term_node.tid = 1
GROUP BY n.nid 
ORDER BY av_avg DESC');

This isn't tested but should come close to what you need.
Cheers

Stuart Greenfield’s picture

Stuart Greenfield commented

I wanted something similar and used this page from the PHP Snippets handbook to get me started.

You need to create a join between {node} and {term_node} so that only nodes matching the term you want come back. The linked example shows how to return nodes from one, or many, terms.

Just five more minutes...

www.stuartandnicola.com

Cosmos-1’s picture

Cosmos-1 commented

thanks for your help.. but its just displaying nothing :/ ill have to read up on this stuff i guess.

Cosmos-1’s picture

Cosmos-1 commented

i cant get it.. if anyone can figure this out.. i really think there would be high demand for this.

gollyg’s picture

gollyg commented

okay - so what is actually happening? Is is still generating errors? is it returning result set?

Stuart Greenfield’s picture

Stuart Greenfield commented

This code block is lifted straight from my homepage. I use this to fetch the three most recent nodes from three specific categories.

// Retrieve from categories 4, 5 and 8 first
  $taxo_id = '4,5,8';
  $sql = "SELECT DISTINCT n.nid FROM {node} n INNER JOIN {term_node} t ON n.nid = t.nid WHERE t.tid IN ($taxo_id) AND n.status = 1 AND n.nid NOT IN ($usednodes) ORDER BY n.created DESC";
  $result = db_query_range(db_rewrite_sql($sql), 0, 3);

  while ($node = db_fetch_object($result)) {
    if ( $onfrontpage ) $output .= node_view(node_load(array('nid' => $node->nid), NULL, TRUE), 1);
    $usednodes .= ','.$node->nid;
  }

Note that I have to use SELECT DISTINCT because I'm retrieving from multiple categories. The same node might be in several categories so I'd get it several times which isn't what I want! If you are just retrieving from one category then you could just use SELECT

The bit about AND n.nid NOT IN ($usednodes) is just for my site - I use it to make sure I don't retrieve nodes I've already used earlier in the home page.

The while crops up in loads of the examples as a means of loading a series of nodes from a database result.

The if ( $onfrontpage ) at the start of the output line is again specific to how I'm generating my home page. You can ignore that and just use output .= onwards.

Does that help??

________

Just five more minutes...

www.stuartandnicola.com