Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I would like to list my posts by a specific taxonomy. At the same time, for each taxonomy, I woud like to count the number of posts with a specific custom field value. So the list would look like this: for each: custom taxonomy name / count of posts / count of posts in this taxonomy that contains custom post field (key) with specific value

All I have been able to do so far is to list custom taxonomy and the count of posts (simply by listing category or by wp database query) or to list posts with specific meta_value for meta_key. I couldn't combine both though:

for the custom taxonomy list and post count:

 $variable = wp_list_categories('taxonomy=organy&echo=0&show_count=1&title_li=&');
$variable = preg_replace('~\((\d+)\)(?=\s*+<)~', '<span class="rank-nbr">$1</span>', $variable);
echo $variable;

for the custom field's value post count:

$status_count = $wpdb->get_var("SELECT COUNT(*) FROM $wpdb->postmeta WHERE meta_value = 'fixed';");
echo '<p>number of posts: ' . $status_count . '</p>';

I figure it should be done by wpdb query.

I would appreciate help from more advanced programmers...

My another attempt is:

$terms = get_terms("MY_TAXONOMY_TERM");
$count = count($terms);
$meta_count = $wpdb->get_var("SELECT COUNT(*) FROM $wpdb->postmeta WHERE meta_value = 'MY_SPECIFIC_VALUE';" );

if ( $count > 0 ){
echo "<tr>";
foreach ( $terms as $term ) {
echo "<td>" . $term->name  ,"</td><td> ",  $term->count . "</td><td> ",  $meta_count, "<td>";

}
echo "</tr>";
}

But here what happens is the list of all the posts with a specific value (MY_SPECIFIC_VALUE).

share|improve this question

1 Answer 1

up vote 0 down vote accepted

The solution is:

global $wpdb;

$terms = get_terms("TAXONOMY_TERM");
$count = count($terms);

if ( $count > 0 ){
foreach ( $terms as $term ) {

$querystr = "

SELECT count(ID) as count FROM $wpdb->posts
LEFT JOIN $wpdb->term_relationships ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id)
LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
WHERE exists ( SELECT * FROM $wpdb->postmeta WHERE ($wpdb->postmeta.post_id = $wpdb->posts.ID) AND meta_key = 'META_KEY' and meta_value = 'META_VALUE' )
AND (post_status = 'publish' )
AND $wpdb->term_taxonomy.taxonomy = 'TAXONOMY_TERM'
AND $wpdb->term_taxonomy.term_id = '".$term->term_id."'
";

$res= $wpdb->get_results($querystr);

echo "<tr><td>" . $term->name  ,"</td><td> ",  $term->count , "</td><td> ",  $res[0]->count, "</td></tr>";
}
}
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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