CODEWITHSUNDEEP
wordpress
User_FTW
User_FTW

Reputation: 524

Count number of posts for a user from a custom taxonomy that has a specific term meta value (WordPress)

I'm trying to display a count (for logged in user) that shows how many books they have in the library.

library is the name of the custom taxonomy, and the returned count should only be for termmeta where the meta key library_status does not have a value of archived.

Here's my code:

$args = array(
    'post_type'         => 'book',
    'post_status'       => array('publish', 'private'),
    'author'            => get_current_user_id(),
    'posts_per_page'    => -1,
    'meta_key'          => 'book_status',
    'meta_value'        => array('written', 'un-written', 'on-hold', 'under-review'),
    'tax_query' => array(
        array(
            'taxonomy' => 'library'
        )
    ),
    'meta_query'        => array(
        array( 
            'key'     => 'library_status',
            'value'   => 'archived',
            'compare' => 'NOT LIKE'
        )
    )
);
$posts = new WP_Query($args);
$my_posts_count = $posts->post_count;
return $my_posts_count;

At the moment it always returns nothing. How can I fix this?

Upvotes: 0

Views: 116

Answers (1)

silver
silver

Reputation: 5311

You cant accomplish this with using WP_Query it does not have support for termmeta

the meta_query you define will query the meta of the object for the table you are querying, since WP_Query will query on posts_table, the meta_query will query the wp_postmeta table

you need to use RAW sql query, Something like below(untested)

SELECT COUNT(p.ID) /* count the id result*/

FROM wp_posts p /* query post table*/

/* inner join table that holds postmeta with meta_key and meta_value filter*/
INNER JOIN wp_postmeta pm 
    ON pm.post_id = p.ID 
    AND pm.meta_key = 'book_status'
    AND pm.meta_value IN ('written', 'un-written', 'on-hold', 'under-review')

/* inner join table that holds relatinship between post and taxonomy*/
INNER JOIN wp_term_relationships tr ON tr.object_id = p.ID 

/* inner join table that holds the taxonomy and filter by name*/
INNER JOIN wp_term_taxonomy tt 
    ON tt.term_id = tr.term_taxonomy_id 
    AND tt.taxonomy = 'library' 

/* inner join table that holds the termmeta and filter by key and value */
INNER JOIN wp_termmeta tm 
    ON tm.term_id = tt.term_id 
    AND tm.meta_key = 'library_status'
    AND tm.meta_value NOT IN ('archived') # or use != operator AND tm.meta_value != 'archived' 

/* Additional post filter */
WHERE  p.post_type = 'book'
AND p.post_status IN ('publish', 'private')
AND p.post_author = 1

Paste it on SQL tab inside PhpMyAdmin and make sure you get the correct result you desire, then you can use use it on wodpress $wpdb like so;

global $wpdb;
$id = get_current_user_id();
return $wpdb->get_results("SELECT COUNT(p.ID)
FROM {$wpdb->prefix}posts p
.
.
.
AND p.post_author = {$id}
.
.
.
");

Upvotes: 1

Related Questions