0

I am trying to get count of a custom post type with a specific custom field value from wpdb without doing a WP_Query or get_posts since I think it is more resource efficient and I need to return 10 different counts for 10 different values on one page.

I can't get the right count to show up. A number is returned, but it is wrong. Here is the code :

$meta_value = key($choices);
        $count = $wpdb->get_var(
            $wpdb->prepare (
            "
            SELECT  count(ID) 
            FROM    $wpdb->posts, $wpdb->postmeta
            WHERE   $wpdb->posts.post_status = 'publish'
                    AND $wpdb->posts.post_type = 'tasks'
                    AND $wpdb->postmeta.meta_key = 'task_status'
                    AND $wpdb->postmeta.meta_value = %s
            ",
            $meta_value
        )); ?>

How can I make it right? key($choices) returns the right value (string) for the $meta_value variable. It is my absolute first use of $wpdb so have mercy.

Edit: solved by adding INNER JOIN:

"
        SELECT  count(ID) 
        FROM    $wpdb->posts
        INNER JOIN $wpdb->postmeta
            ON $wpdb->posts.ID = $wpdb->postmeta.post_id
        WHERE   $wpdb->posts.post_status = 'publish'
                AND $wpdb->posts.post_type = 'tasks'
                AND $wpdb->postmeta.meta_key = 'task_status'
                AND $wpdb->postmeta.meta_value = %s
        "
  • 1
    this is really a SQL question, you have to join the post and post meta tables on the ID, as-is you haven't established any relationship between the posts and post meta. anyway, rather than do this with wpdb, I'd just use WP_Query on the admin side when a post is updated and save it to an option, removing the need to query for anything but the option on the front end. – Milo Jan 2 '14 at 18:28
  • Thanks for pointing me in the right direction, INNER JOIN on post id solved the problem. – Maija Vilkina Jan 2 '14 at 18:42
1

While custom SQL is sometimes what it takes, note that to address your concern for WP_Query it supports 'fields' => 'ids' argument to limit data returned to IDs (which can be easily used for count).

In many cases that is reasonably efficient while more robust and future-proof in WP environment.

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.