I'm about to create a Tag cloud module that shall have a block showing a list of all terms. The most used terms shall be in big text; the terms that not used often shall be in small text, as in the following screenshot.

I want to have a $count variable that holds the count of how many times each term is used.

I think that I can do so:

$database = \Drupal::database();
$terms_count = $database
->query('SELECT COUNT(tid) FROM {taxonomy_index}')

foreach($terms_count as $term_count) {
  print $term_count;

But it doesn't show anything.

  • 1
  • Actually I felt the other one was more clear. This post contains a lot of data, where you are really only after the query. Simpler narrow scoped questions are often preferred. – Neograph734 Sep 18 '17 at 12:44
  • I have corrected it now, do you think that this is more clear? – user72672 Sep 18 '17 at 13:02
  • 1
    Yes absolutely. It is much easier to see what you need now. :) – Neograph734 Sep 18 '17 at 13:04
  • By the way, I think you should wrap your query in quotes, like this query('SELECT ... '). – Neograph734 Sep 18 '17 at 13:05

Here's the query you're going for.

SELECT count(*) AS count, tid FROM taxonomy_index GROUP BY tid ORDER BY count DESC LIMIT 100;

You'll need to use AddExpression to get the count in there.

$query = $this->database->select('taxonomy_index', 'ti');
$query->addField('ti', 'tid', 'tid');

$query->addExpression("count(*)", 'count');
$query->orderBy('count', 'DESC');
$query->range(0, 50);

$data = $raw_data = $query->execute()->fetchAll();

Here's how you might convert the data to a render array:

$term_storage = \Drupal::entityTypeManager()->getStorage('taxonomy_term');

$build = [];
foreach ($data as $count_group) {
  $term = $term_storage->load($count_group->tid);
  $label = $term->label();
  $link = $term->toLink();
  $link->setText($this->t($label . ' (' . $count_group->count . ')'));
  $build[] = $link->toRenderable();

return $build;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy