0

I need to find out the number of times a term/tag or category is used in a post in WordPress. I wrote the below SQL query.

SELECT t.name , count(*)FROM wp_terms t
JOIN wp_term_taxonomy tt ON tt.term_taxonomy_id = t.term_id
JOIN wp_term_relationships tr ON tr.term_taxonomy_id = tt.term_taxonomy_id
JOIN wp_posts p ON p.id=tr.object_id
Where t.name='3d'

This gives me a count of '1011' I know this is wrong because I randomly checked the posts which I got and the term was not used. Please advice where I might be going wrong?

4
  • Aggregate functions like count and sum should be accompanied by group by. If you are not using then most probably you are not doing it right!! – sri Nov 21 '13 at 15:15
  • I use group by for all the terms. For the sake one asking a specific question I have changed it for only one term here – Vishal Khialani Nov 21 '13 at 15:17
  • If you like experiments and you got some various conditions, you might try this one ;-) – birgire Nov 21 '13 at 15:33
  • As-written, this is a SQL query question, and not specific to WordPress. "Please edit my SQL query" is not a good fit for WPSE. – Chip Bennett Nov 21 '13 at 18:26
2

Wp_Query should be able to do this for you. Pass it the appropriate parameters, including a tax_query, and check found_posts.

$p = new WP_Query(
  array(
    'post_type' => 'custcpt', // your CPT
    'tax_query' => array(
      array(
        'taxonomy' => 'custtax', // your tax
        'field' => 'id',
        'terms' => $cat->term_id, // your term ID
      )
    ), 
    'ignore_stickie_posts' => true,
    'fields' => 'ids',
  )
);
echo $p->found_posts;

Untested, but should be close.

3
  • I have never coded in wp but I think my sql logic seem to be similar to what you have given. Pls give your opinion on my sql logic. I am trying to avoid coding in wp. – Vishal Khialani Nov 21 '13 at 17:01
  • 4
    When in WordPress, avoiding WordPress is a bad idea. The database structure is complicated, especially for taxonomies, and it can change. If you use Core functions, your code will survive the change. You can use var_dump($p->request); to inspect the SQL for yourself. – s_ha_dum Nov 21 '13 at 17:41
  • 2
    "Pls give your opinion on my sql logic." - I'm unsure why a site of WordPress experts would have a particular opinion on SQL logic? – Chip Bennett Nov 21 '13 at 18:27
0

If the taxonomy is only attached to one post type, you can just use the term_taxonomy.count table column.

SELECT t.name , tt.count FROM wp_terms t
JOIN wp_term_taxonomy tt ON tt.term_taxonomy_id = t.term_id
Where t.name='3d'

Should do the trick.

0
0

I solved it:

SELECT t.name, count(*) FROM wp_terms t
JOIN wp_term_taxonomy tt ON tt.term_id = t.term_id
JOIN wp_term_relationships tr ON tr.term_taxonomy_id = tt.term_taxonomy_id
JOIN wp_posts p ON p.id=tr.object_id
WHERE t.name = '3d'

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.