By jo1ene on 
$terms = taxonomy_node_get_terms(arg(1)); //get terms from current node
  foreach($terms as $term){
    $tids[] = $term->tid;
  }
$matches = db_result(db_query(db_rewrite_sql("SELECT nid FROM {node} INNER JOIN {Term_node} ON node.nid = term_node.nid WHERE node.type = 'foo' AND term_node.tid = $tids"))); //$tids???

I am finding the terms associated with the current node in the first statement - BTW in my case, there should always be 2 terms. I then take those terms, and want to find out how many nodes of type "foo" also match those (2) terms. I know this code is wrong (particularly the WHERE stuff) as I have never studied SQL in earnest. I just put a best effort forward for the sake of discussion.

Based on the number of matches eg. count($matches), I will do one of two things. That part I understand (more).

Anyone???

⋅ Categories: Drupal 5.x

Comments

nevets’s picture

nevets commented

A couple of things since node and term_node have nid has a field you need to select with node.nid or term_node.nid, the example below aliases node and term_node and selects n.nid. Also to match to one of several values of tid you want 'tid IN (some set)' where (some set is a comma seperated list of values, we get the values by taking the array $tids and converting it to a comma seperated string with implode().

$node_type = 'foo';  // Set to the node type you want to match on
$tids = array();
$terms = taxonomy_node_get_terms(arg(1)); //get terms from current node
  foreach($terms as $term){
    $tids[] = $term->tid;
  }
if ( empty($tids) ) {
  // Node has no terms set
}
else {
  $set = implode(",", $tids);
  $matches = db_query(db_rewrite_sql("SELECT n.nid FROM {node} n INNER JOIN {term_node} tn ON n.nid = tn.nid WHERE n.type = '%s' AND tn.tid IN (%s)"), $node_type, $set);
  while ( $data = db_fetch_object($matches) ) {
    // Do something with the nid, here we just print it
    print $data->nid . '<br />';
  }
}
jo1ene’s picture

jo1ene commented

This is what I was aiming for. I put this in a node with PHP filter to see what the output was - to see if it was finding the nids I wanted. It was indeed finding nodes of the right node type that matched the terms.

But it appears to be doing an OR match rather than an AND match. So I get a list of nids - some which match one term, some that match the other, and one that is listed twice because it matches both. What I would expect to come up is ONLY ONE nid - the one that is listed twice - because that is the only node (in this test case) that matches BOTH terms. So the query should be finding nodes that match both tids in the set.

I am going to try and figure this out more. Thanks for your input. I feel a lot closer to solving my issue.

Advanced Web Design

jo1ene’s picture

jo1ene commented 
$matches = db_query(db_rewrite_sql("SELECT n.nid FROM {node} n INNER JOIN {term_node} tn ON n.nid = tn.nid WHERE n.type = '%s' AND tn.tid = '%s'"), $node_type, $set);

This gives me more hwat I'm looking for although I don't understand why. Accident? I'll keep testing.

Advanced Web Design

jo1ene’s picture

jo1ene commented 
$node_type = 'ad';  // Set to the node type
$tids = array();
$terms = taxonomy_node_get_terms(arg(1)); //get terms from current node 
  foreach($terms as $term){
    $tids[] = $term->tid;
  }
if ( empty($tids) ) {
  // Node has no terms set
}
else {
  $set = implode(",", $tids);  //will always be 2 terms, one from each vocab
  $matches = db_query(db_rewrite_sql("SELECT n.nid FROM {node} n INNER JOIN {term_node} tn ON n.nid = tn.nid WHERE n.type = '%s' AND tn.tid IN (%s)"), $node_type, $set);
  while ( $data = db_fetch_object($matches) ) {
    $x++;
  }

  $matches2 = db_query(db_rewrite_sql("SELECT DISTINCT n.nid FROM {node} n INNER JOIN {term_node} tn ON n.nid = tn.nid WHERE n.type = '%s' AND tn.tid IN (%s)"), $node_type, $set);
  while ( $data = db_fetch_object($matches2) ) {
    $y++;
  }
  $z = $x - $y; //if there is a difference, there was at least one duplicate eg. an AND match, a nid matching both terms in the set

  if ($z >= 1) {
    $tids = implode(',', $tids); //there's a node that matched both tids so do AND matching
  }
  else {
    $tids = implode('+', $tids); //there aren't so do OR matching
  }
  return array($tids);
}

The real issue is that I don't care what the matches are, only that there is at least one node that matches both terms. By doing a regular SELECT and a DISTICT - and finding a difference in the counts, if any - I can see if there are any nids that come up twice.

In the end, I am trying to match ads using the advertisement module and views module. By using this in the arguments code for Term: Term ID, I can either pass tids with ',' or '+' to get AND or OR matching where appropriate.

This code is probably (definately) way clunky, but it makes the client happy for now. Any cleanup is appreciated.

Advanced Web Design

nevets’s picture

nevets commented

If you expect 2 and always just 2 terms this should work

$node_type = 'foo';  // Set to the node type you want to match on
$tids = array();
$terms = taxonomy_node_get_terms(arg(1)); //get terms from current node
  foreach($terms as $term){
    $tids[] = $term->tid;
  }
if ( count($tids)  != 2 ) {
  // Node has no terms set
}
else {
  $tid1 = $tids[0];
  $tid2 = $tids[1];
  $matches = db_result(db_query(db_rewrite_sql("SELECT COUNT(n.nid) FROM {node} n INNER JOIN {term_node} tn1 ON n.nid = tn1.nid INNER JOIN {term_node} tn2 ON n.nid = tn2.nid WHERE n.type = '%s' AND tn1.tid = %d AND tn2.tid = %d"), $node_type, $tid1, $tid2));
  // $matches now has the number of matches
  print "There are $matches matches";
}
jaarong’s picture

jaarong commented

Is there a way to do this using views and panels now?