By bermin on

I have been playing with the following snippet and I can't seem to make it display the complete list of Vocab terms including those without any nodes attributed to it. (currently shows only terms with a node or more attributed). What would I have to change to the following code to display all Vocab terms for id=1?

$vocabulary_id = 1;
$result = db_query("SELECT d.tid, d.name, MAX(n.created) AS updated, COUNT(*) AS count FROM {term_data} d INNER JOIN {term_node} USING (tid) INNER JOIN {node} n USING (nid) WHERE d.vid = $vocabulary_id AND n.status = 1 GROUP BY d.tid, d.name ORDER BY updated DESC, d.name");
$items = array();
while ($category = db_fetch_object($result)) {
$items[] = l($category->name .' ('. $category->count .')', 'taxonomy/term/'. $category->tid) .'<br />'. t('%time ago', array('%time' => format_interval(time() - $category->updated)));
}
print theme('item_list', $items);

Comments

nevets’s picture

nevets commented

To get all the terms you first need to SELECT from term_data and the inside the loop, for each tid, get the information about how many nodes and last created. The following code will do this, though it only sorts on term name (not last created).

<?php
$vocabulary_id = 1;
$result = db_query("SELECT d.tid, d.name FROM {term_data} d WHERE d.vid = $vocabulary_id GROUP BY d.tid, d.name ORDER BY d.name");
$items = array();
$sql = "SELECT MAX(n.created) AS updated, COUNT(n.nid) AS count FROM {term_node} tn INNER JOIN {node} n USING (nid) WHERE tn.tid = %d AND n.status = 1";
while ($category = db_fetch_object($result)) {
$stats = db_fetch_object(db_query($sql, $category->tid));
if ( $stats->count ) {
  $append1 = ' ('. $stats->count .')';
  $append2 = '<br />'. t('%time ago', array('%time' => format_interval(time() - $stats->updated)));
}
else {
  $append1 = ' (no content)';
  $append2 = '';
}
$items[] = l($category->name . $append1, 'taxonomy/term/'. $category->tid) . $append2;
}
print theme('item_list', $items);
?>
shawn dearmond’s picture

shawn dearmond commented

I did this, but now it displays all nodes in that category, regardless of whether the user has access to those nodes or not. Is there a way to search so that it only gives the count that they have access to?

javanaut’s picture

javanaut commented

I haven't tried this out, but I would suggest using a LEFT OUTER JOIN on the term_node table. You'll have to rearrange some things, but it would look something like this:

SELECT d.tid, d.name, MAX(n.created) AS updated, COUNT(*) AS count 
FROM {term_data} d <strong>LEFT OUTER JOIN</strong> {term_node} USING (tid) <strong>LEFT OUTER JOIN</strong> {node} n USING (nid) 
WHERE d.vid = $vocabulary_id AND <strong>(</strong>n.status = 1 <strong>OR n.status IS NULL)</strong> GROUP BY d.tid, d.name ORDER BY updated DESC, d.name

But the updated and count columns may get wonky for the empty values. I would imagine your display logic would change based on $category->updated...

$vocabulary_id = 1;
$result = db_queryd("SELECT d.tid, d.name, MAX(n.created) AS updated, COUNT(*) AS count FROM {term_data} d LEFT OUTER JOIN {term_node} USING (tid) LEFT OUTER  JOIN {node} n USING (nid) WHERE d.vid = $vocabulary_id AND (n.status = 1 OR n.status IS NULL) GROUP BY d.tid, d.name ORDER BY updated DESC, d.name");
$items = array();
while ($category = db_fetch_object($result)) {
  if($category->updated) {
    $items[] = l($category->name .' ('. $category->count .')', 'taxonomy/term/'. $category->tid) .'<br />'. t('%time ago', array('%time' => format_interval(time() - $category->updated)));
  }
  else {
    $items[] = l($category->name .' (0)', 'taxonomy/term/'. $category->tid) .'<br /><em>no items</em>';
  }
}
print theme('item_list', $items);