How to count how many times a meta_value appears in a column by certain meta_key?

Question

I'm trying to query how many times each record exists on a column in my table from database in WordPress and export that column. How would I do this?

In the excel exported I want to have the column from meta_value with meta_key "user_valid" and a column with the count of how many times that meta_value exists in the mysql column.

meta_key          meta_value

user_valid        '1, 2, 3'      
user_valid        '1, 2, 1' 
user_valid        '1, 2, 3'

For exporting the column 'meta_value' with the meta_key 'user_valid' I used:

SELECT meta_value FROM `us_test` WHERE meta_key = 'user_valid'

And I export with the export button from MYSQL after using this query.

I don't know how to do the query for the other column.

I think it should be something like this but I'm not sure, because when I use the next query it doesn't return all the records:

SELECT meta_value, COUNT( * ) c FROM `us_test` WHERE meta_key = 'user_valid' GROUP BY meta_value

Answer 1

You have two options here, the Wordpress way and the other way, SQL.

Just before I start, you should never ever hardcode a db name, the issue is, you would need to change the db name in all queries where it is hardcode should you move the code to another website with a different db name, also when you change the db name on the site you are working on. This can set you on a wild goose chase should you forget about that

You should always always sanitize and validate input data to ensure it is safe to prevent any malicious code being injected into your site. SQL injection is common and many hackers use SQL injection to hack a site

SQL

SQL gives one a bit less control as it is less dynamic. For instance, if you need get a count for a certain term only, you would need to alter the query directly or you will need to implement some kind of filter system.

As I have stated, never ever hardcode db names, rather use the wpdb class to set the prefix. This will avoid issues going forward. Also, as I said, you have to sanitize to avoid SQL injection, in this case, we will use the prepare method of the wpdb class to take care of sanitation and will safeguard against SQL injection.

Here is the function, which I have commented here and there to make sense:

function get_post_meta_key_count( $key = '', $value = '', $type = 'post', $status = 'publish' ) 
{
    // Use $wpdb to avoid bugs and errors, do not hardcode db names
    global $wpdb;

    if( empty( $key ) )
        return;


    // Set the default WHERE clause where we return the count of the key regardless of value
    $where = $wpdb->prepare( 
        "
            pm.meta_key = '%s' 
            AND p.post_type = '%s' 
            AND p.post_status = '%s'
        ", 
        $key, 
        $type, 
        $status 
    );

    // If a value is specified, add that to the WHERE clause
    if ( $value ) {
        $where .= $wpdb->prepare( 
            "
                AND pm.meta_value = '%s'
            ", 
            $value 
        );
    }

    // Query the db to return the post count according to key and value if value is set
    $count = $wpdb->get_var(
        "
            SELECT count(DISTINCT pm.post_id)
            FROM {$wpdb->postmeta} pm
            JOIN {$wpdb->posts} p ON (p.ID = pm.post_id)
            WHERE {$where}
        "
    );

    return $count;
}

What I have done here is, I have set a parameter if you ever need to get the count of a specific meta value of a specific meta key.

USAGE

You can now use the function as follow:

• echo get_post_meta_key_count( 'my_key' ); for post count of meta_key my_key for the default post type post and only published posts

• echo get_post_meta_key_count( 'my_key', '', 'custom_post_type', 'trash' ); for post count of meta_key my_key for the custom post type custom_post_type and only trashed posts

• echo get_post_meta_key_count( 'my_key', 'my_value' ); for post count of meta_key my_key and for the meta_value, my_value for the default post type post and only published posts

WP_Query

If you are looking for a build in way to do this (which should always be your first option), you can use the WP_Query class to do it.

The issue with using WP_Query is that, it can be really expensive if not used correctly. Many people avoid WP_Query because of this, or unknowingly runs queries that are quite expensive and unnecessary. So will look at a way to make this as fast as a custom SQL query.

The real advantage of WP_Query above a custom SQL query is, you can adjust the query by simply passing in the parameters needed, WP_Query will always take care of the hard work to construct the proper SQL query according to the passed parameters.

Lets look at optimizing WP_Query in order to just return a post count

• Firstly, we will only query one single post. WP_Query, by default, is build such that no matter how many posts are queried (1, 100 or all posts), WP_Query will continue to look for all posts matching the query, even though it has already found and returned the queried amount of posts. The reason for this is pagination. In order to correctly calculate pagination, WP_Query needs to know how many posts are there which matches the query.

  So WP_Query continues to look through the db after returning the amount of posts queried in order to count all posts that matches the query. This post count is stored in the $found_posts property of the query, and it is this number that is used in conjunction with posts_per_page which is used to calculate how many pages there would be.

  This does not work for get_posts, although get_posts uses WP_Query. get_posts passes 'no_found_rows' => true to WP_Query which breaks the query as soon as the amount of queried posts are found. This legally breaks pagination, that is why get_posts are such a huge headache to paginate correctly

• Secondly, we will only query the single post's ID we need to query, not the complete WP_Post object. This saves on query time.

So lets look at this function:

function get_post_meta_key_count( $key = '', $value = '', $args = [] ) 
{
    // If the $key value is empty, bail
    if( empty( $key ) )
        return;

    // Set the defaults and override any value set for the specific default
    $args['posts_per_page'] = 1;
    $args['fields']         = 'ids';

    if ( $value ) {
        $args['meta_query'][] = [
            'key'   => $key,
            'value' => $value
        ];
    } else {
        $args['meta_query'][] = [
            'key' => $key,
        ];
    }
    $q = new WP_Query( $args );

    // Return the post count
    return $q->found_posts;
}

USAGE

WP_Query, by default, uses the post post type and publish as post status, so we do not need to set this for normal published posts. I have included a third parameter here called $args, this parameter accepts the same exact parameters as the WP_Query class as these are all passed directly to WP_Query. So you can add any parameters here to get a count from from a specific meta key or value

• echo get_post_meta_key_count( 'my_key' ); for post count of meta_key my_key for the default post type post and only published posts

• echo get_post_meta_key_count( 'my_key', 'my_value' ); for post count of meta_key my_key and for the meta_value, my_value for the default post type post and only published posts

• echo get_post_meta_key_count( 'my_key', 'my_value', ['post_type'=>'cpt', 'cat'=>1] ); for post count of meta_key my_key and for the meta_value, my_value for the custom post type cpt and only published posts from the category ID 1

PERFORMANCE TESTING ON meta_key with 24 posts

• SQL -> 1 query in +/- 0.005 seconds

• WP_Query -> 2 queries in +/- 0.012 seconds

As you can see, there is a minute difference in performance, so the WP_Query method will easily be the best option here as it is much more dynamic, even though just a tad slower

Answer 2

So, I will answer my own question:

The query from my question is absolutely correct:

SELECT meta_value, COUNT( * ) c FROM `us_test` WHERE meta_key = 'user_valid' GROUP BY meta_value