getting all values for a custom field key (cross-post)

Question

I know how to get a custom field value for a specific post.

get_post_meta($post_id, $key, $single);

What I need is to get all the values associated with a specific custom post key, across all posts.

Anyone knows of an efficient way to do this? I wouldn't want to loop through all post id's in the DB.

Example:

4 posts all with different values for a custom field called 'Mood'. 2 posts have the value 'happy', 1 post have 'angry' and 1 post has 'sad'

I want to output : across all posts we have: two happy, one angry and one sad author(s).

But for LOTS of posts.

What I'm looking for is either:

• a WP function to get this. or
• a custom query to get this as efficiently as possible.

Answer 1

One possible approach would be to use one of the helper methods in the WPDB class to do a more refined meta-based query.

Using the $wpdb function get_col it's possible return a simple flat array of data.

Here's an example function which queries the database for all posts of a specified post type, post status and meta key (or custom field to the less technically minded).

function get_meta_values( $meta_key = '', $post_type = 'post', $post_status = 'publish' ) {
    
    global $wpdb;
    
    if( empty( $meta_key ) )
        return;
    
    $meta_values = $wpdb->get_col( $wpdb->prepare( "
        SELECT pm.meta_value FROM {$wpdb->postmeta} pm
        LEFT JOIN {$wpdb->posts} p ON p.ID = pm.post_id
        WHERE pm.meta_key = %s 
        AND p.post_type = %s 
        AND p.post_status = %s 
    ", $meta_key, $post_type, $post_status ) );
    
    return $meta_values;
}

So for example, if you would like to find out which posts have a meta key of rating, for the post type movies and to store that information inside a variable, an example of such a call would be.

$movie_ratings = get_meta_values( 'rating', 'movies' );

If you wanted to do little more than print that data to the screen, PHP's implode function can quickly condense the array into a string.

// Print the meta values separated by a line break
echo implode( '<br />', get_meta_values( 'YOURKEY' ));

You can also use the returned data to work out how many posts have the meta values by doing a simple iteration(loop) over the returned data and building an array of the counts, for example.

$movie_ratings = get_meta_values( 'rating', 'movies' );
if( !empty( $movie_ratings ) ) {
    $num_of_ratings = [];
    foreach( $movie_ratings as $meta_value ) {
        $num_of_ratings[$meta_value] = isset( $num_of_ratings[$meta_value] ) ? $num_of_ratings[$meta_value] + 1 : 1;
    }
}

// Output the number of ratings
printf( '<pre>%s</pre>', print_r( $num_of_ratings ) );  

/*
Output:
Array(
    [5] => 10
    [9] => 2
)
ie. there are 10 movie posts with a rating of 5 and 2 movie posts with a rating of 9.
*/

This logic could be applied to various kinds of data, and extended to work any number of different ways. So I hope my examples have been helpful and simple enough to follow.

---

Using transients to cache the results

And here's an updated version that uses WordPress transients to cache the query, as that seems to be the main criticism for using $wpdb in other provided answers.

function get_meta_values( string $meta_key, string $post_type = 'post', bool $distinct = false, string $post_status = 'publish' ) {
    
    global $wpdb, $wp_post_types;
    
    if( !isset( $wp_post_types[$post_type] ) )
        // Existing WP string, it should translate as is
        return __( 'Invalid post type.' ); 
    
    $transient_key = 'get_' . $wp_post_types[$post_type]->name . '_type_meta_values';
   
    $get_meta_values = get_transient( $transient_key );

    if( true === (bool)$get_meta_values )
        return $get_meta_values;
    
    $distinct = $distinct ? ' DISTINCT' : '';
    
    $get_meta_values = $wpdb->get_col( $wpdb->prepare( "
        SELECT{$distinct} pm.meta_value FROM {$wpdb->postmeta} pm 
        LEFT JOIN {$wpdb->posts} p ON p.ID = pm.post_id 
        WHERE pm.meta_key = %s 
        AND p.post_type = %s 
        AND p.post_status = %s 
    ", $meta_key, $post_type, $post_status ) );
    
    set_transient( $transient_key, $get_meta_values, DAY_IN_SECONDS );

    return $get_meta_values;
}

The DAY_IN_SECONDS constant is one of various time in seconds constants setup by WordPress.

Updated argument and variable names to make them more consistent with WordPress naming and also implemented DISTINCT as an optional parameter following the tip from Howdy_McGee in the comments.

Answer 2

It is not good or needed to use the global $wpdb:

// function to grab all possible meta values of the chosen meta key.
function get_meta_values( $meta_key,  $post_type = 'post' ) {

    $posts = get_posts(
        array(
            'post_type' => $post_type,
            'meta_key' => $meta_key,
            'posts_per_page' => -1,
        )
    );

    $meta_values = array();
    foreach( $posts as $post ) {
        $meta_values[] = get_post_meta( $post->ID, $meta_key, true );
    }

    return $meta_values;

}

$meta_values = get_meta_values( $meta_key, $post_type );

Answer 3

I'd just like to add one tiny thing to t31os's code above. I changed "SELECT" into "SELECT DISTINCT" to eliminate duplicate entries when I used this code myself.

Answer 4

For getting all meta values by a meta key

Check wp->db wordpress codex

$values = $wpdb->get_col("SELECT meta_value
    FROM $wpdb->postmeta WHERE meta_key = 'yourmetakey'" );

Answer 5

the fastest way would be a custom sql query and i'm not sure but you can try

$wpdb->get_results("
  SELECT posts.* , COUNT(*) 'moodcount'
  FROM $wpdb->posts as posts
  JOIN $wpdb->postmeta as postmeta
  ON postmeta.post_id = posts.ID
  AND postmeta.meta_key = 'Mood'
  GROUP BY postmeta.meta_key
");

If anything then its a start.

Answer 6

There's no reason why you can't merge t31os and Bainternet's code to have a reusable prepared statement (wordpress style) that returns the count and the values in one efficient operation.

It's a custom query but it's still using the wordpress database abstraction layer - so for example it doesn't matter what the table names really are, or if they change, and it's a prepared statement so we're that much safer from SQL attacks etc.

In this instance I'm no longer checking for post type and I'm excluding empty strings:

    $r = $wpdb->get_results(  $wpdb->prepare( "
        SELECT pm.meta_value AS name, count(*) AS count  FROM {$wpdb->postmeta} pm
        LEFT JOIN {$wpdb->posts} p ON p.ID = pm.post_id
        WHERE pm.meta_key = '%s'
        AND pm.meta_value != '' 
        AND p.post_type = '%s'
        GROUP BY pm.meta_value
        ORDER BY pm.meta_value          
        ", $key, $type) 
        );
    return $r;

In this particular is

This will return an array of objects like so:

array  
 0 => 
 object(stdClass)[359]
  public 'name' => string 'Hamish' (length=6)
  public 'count' => string '3' (length=1)
 1 => 
 object(stdClass)[360]
  public 'name' => string 'Ida' (length=11)
  public 'count' => string '1' (length=1)
 2 => 
 object(stdClass)[361]
  public 'name' => string 'John' (length=12)
  public 'count' => string '1' (length=1)

Answer 7

Use the following with foreach

 $key = get_post_custom_values( 'key' );

Assumes the named of your custom field key is