Show a list (x) of taxonomy terms with node counts

This simple block snippet displays a list of taxonomy terms in alphabetical order. Each term links to the term's page.

<?php

$vid = 15; //vocabulary id
$num_term = 20; //limit maximum terms

//USE INNER JOIN below to only show terms with a count of 1 or more (the LEFT JOIN includes terms with a count of 0)
$query = "SELECT td.tid AS tid, name, COUNT( nid ) AS count
FROM {term_data} AS td
LEFT JOIN {term_node} AS tn ON td.tid = tn.tid
WHERE td.vid=". $vid ."
GROUP BY tn.tid ORDER BY name ASC LIMIT ". $num_term;

$result = db_query($query);
while ($term = db_fetch_object($result)) {
  $items[]= l($term->name, "taxonomy/term/$term->tid") . ' (' . $term->count . ')';
}

if(count($items)) {
  return theme('item_list',$items);
}

?>

The result will looks like this:

spiritual (4)
yoga (2)
therapy (5)
india (1)

Comments

It shows terms with zero

volocuga commented 8 March 2010 at 00:02

It shows terms with zero nodes. How to solve this?

How can I display the below all the terms & subterms under the Catalog vocabulary with proper indentation? The order/weight is similar on how it is stored under the Catalog vocabulary. Actually this is similar to the Ubercart's Catalog block, but I need just a simpler one to place in on the block. The ubercart's code is very complex and don't know how to retrieve it.

Any help would be welcome. Thanks!

Catalog

Clothing (6)
- Dresses (3)
- Sweaters (1)
- Tops (1)

Bags (2)
- Backpacks (1)
- Hobos (1)

Sample screenshot

Thanks for the great

JonoB commented 21 April 2010 at 14:22

Thanks for the great snippet!

If you want to exclude items with no count, then

replace
$items[]= l($term->name, "taxonomy/term/$term->tid") . '(' . $term->count . ')';

with

if ($term->count > 0) {
  $items[]= l($term->name, "taxonomy/term/$term->tid") . ' (' . $term->count . ')';
}

Installing a php snippet

jnaylor commented 11 May 2010 at 21:55

I am very new to Drupal and php and was wondering, where (which file) do you insert this php snippet into? I would like to add this snippet to a block that I created.

Add new block

husztisanyi commented 14 May 2010 at 10:39

http://yoursite.com/admin/build/block/add
... and choose the php filter!

easier way to eliminate terms with zero count

benlotter commented 5 February 2011 at 22:25

In the code above just replace the LEFT JOIN with an INNER JOIN. I'll go ahead and update the main text.
so
LEFT JOIN {term_node} AS tn ON td.tid = tn.tid
becomes

INNER JOIN {term_node} AS tn ON td.tid = tn.tid

You can also use this module:

kristofvanroy commented 16 May 2010 at 13:52

You can also use this module: http://drupal.org/project/term_node_count which integrates with views

Thanks for the help

jnaylor commented 24 May 2010 at 20:50

Thank you for your help, however, I found another answer that does not use php.

In my view for this content, I added a block view with an argument of "Taxonomy: Term ID" and selected "Summary, sorted ascending". This created exactly what I needed. Hopefully this will also help someone else who was searching for what I was.

More details...

shriji commented 7 October 2010 at 16:19

Hi Jnaylor,

I am just like you. No php experience. Can you please give more details how did you do it?

Thanks for you help.

Problem solved...

shriji commented 8 October 2010 at 17:16

Hi,

After my last post, I started to fiddle around with arguments section of the views and was able to figure out how to do that without coding...thats wonderful....my goal is to get the most out of drupal without writing code....and it is my belief that you can....

Thanks

NOTE this code does not account for unpublished nodes

benlotter commented 5 February 2011 at 22:53

I just came across this and thought I should pass it along. The code in the main block works but does not eliminate node which are unpublished. So your counts will be off if you have unpublished nodes within the vocabulary you are using. You may want to add a join (or replace it?) to the nodes table and check the status field to see if its published.

I have implemented the following changes to my query to make the counts correct for published nodes only (i.e. status = 1).

<?php

$vid = 15; //vocabulary id
$num_term = 20; //limit maximum terms

$query = "SELECT td.tid AS tid, name, COUNT( tn.nid ) AS count
FROM {term_data} AS td
INNER JOIN {term_node} AS tn ON td.tid = tn.tid
INNER JOIN {node} AS n ON n.nid = tn.nid
WHERE td.vid = ". $vid ." AND n.status = 1
GROUP BY tn.tid
ORDER BY name ASC
LIMIT ". $num_term;

$result = db_query($query);
while ($term = db_fetch_object($result)) {
  $items[]= l($term->name, "taxonomy/term/$term->tid") . ' (' . $term->count . ')';
}

if(count($items)) {
  return theme('item_list',$items);
}

?>

NOTE: this code does not account for one's access to the nodes

benlotter commented 5 February 2011 at 22:56

One more thing... I think you would have to use the query rewrite function of Drupal if you want the counts based on the user's access level to the underlying nodes. I don't currently have a need for this since the content I'm working with is publicly available. This is just something to bear in mind.

The above does not account for node revisions

benlotter commented 22 February 2011 at 22:37

If you enable node revisions and use the code above you will get duplicates. The revised version below accounts for this.

<?php

$vid = 3; //vocabulary id
$num_term = 20; //limit maximum terms

$query = "SELECT tid, name, count
FROM (
SELECT td.tid AS tid, name, COUNT(*) AS count
FROM {term_data} AS td
JOIN {term_node }AS tn ON td.tid = tn.tid
JOIN {node} AS n ON n.nid = tn.nid AND n.vid = tn.vid
WHERE td.vid = ". $vid ." AND n.status = 1
GROUP BY td.tid
ORDER BY count DESC
LIMIT ". $num_term . "
) AS t
ORDER BY name ASC";

$result = db_query($query);
while ($term = db_fetch_object($result)) {
if ($term->count > 0) {
  $items[]= l($term->name, "taxonomy/term/$term->tid") . ' (' . $term->count . ')';
}
}

if(count($items)) {
  return theme('item_list',$items);
}

?>

Great code many thanks for

Prancz_Adam commented 20 March 2012 at 10:52

Great code many thanks for it!
I have ony one problem with that solution! It does not listed the empty taxonomy terms!
Have you got any solution for it?

Prancz Ádám
Könyvelés, adótanácsadás, webdesign