Ask your WordPress questions! Pay money and get answers fast! (more info)

List nbr of posts for each taxonomy term & specific meta value WordPress

  • SOLVED

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 (distinct): custom taxonomy term / count of posts / count of posts in this taxonomy that contains custom post field (meta_key) with specific value (meta_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

Answers (1)

2012-11-22

Arnav Joy answers:

show me how you want the result to be listed by giving example


gipson comments:

For instance in a table like this:


<table id="ranking">
<tr>
<td>taxonomy</td>
<td>nbr of posts in this taxonomy</td>
<td>including posts with meta_value "fixed"</td>
</tr>
<tr>
<td>term #1</td>
<td> 2</td>
<td> 2</td>
</tr>
<tr><td>term #2</td><td> 1</td><td> 0</td></tr></table>



Arnav Joy comments:

what is custom field called

<blockquote>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 </blockquote>

provide me all your code


gipson comments:



<table id="ranking">
<tr>
<td>taxonomy</td>
<td>nbr of posts</td>
<td>including "fixed"</td>
</tr>
<?php
$terms = get_terms("cases");
$count = count($terms);
$meta_count = $wpdb->get_var("SELECT COUNT(*) FROM $wpdb->postmeta WHERE meta_value = 'fixed';" );

if ( $count > 0 ){

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

}

}
?>
</table>



This one lists the terms of taxonomy 'cases', the number of posts. however, in the third table column it lists the total number of posts with meta_value 'fixed', instead of number of posts containing this specific value (the key for this value is 'status', btw).


Arnav Joy comments:

try this


<table id="ranking">

<tr>

<td>taxonomy</td>

<td>nbr of posts</td>

<td>including "fixed"</td>

</tr>

<?php

global $wpdb;

$terms = get_terms("cases");

$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 = 'status' and meta_value = 'fixed' )
AND (post_status = 'publish' )
AND $wpdb->term_taxonomy.taxonomy = 'book_taxonomy'
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>";



}



}

?>

</table>


gipson comments:

Thanks. It looks like it works, but when I delete / change this part. Should I delete it or change "book-taxonomy" to "cases"?



AND $wpdb->term_taxonomy.taxonomy = 'book_taxonomy'