54

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.
2
  • 5
    Seems like you're using this as a taxonomy. Why not simply (automatically) add a term to these posts when saving? Would make querying a lot easier.
    – kaiser
    Feb 15, 2011 at 15:11
  • @kaiser I can't thank you enough for being a genius! Oct 26, 2016 at 21:24

7 Answers 7

65

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.

3
  • 3
    Also fun-fact for future viewers, if you want to pull only Unique meta values - you type DISTINCT right after the SELECT in the function above. Could be useful.
    – Howdy_McGee
    Feb 7, 2014 at 18:24
  • I think this is extremely useful May 1, 2017 at 14:54
  • How to do this, and return the values sorted?, I think that using ORDER by but I cant figure out how to use it
    – efirvida
    Apr 17, 2018 at 3:43
20

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 );
4
  • This would be my preferred method of doing it, in most cases. It makes five queries, rather than just one, but, as it's using the standard WordPress procedures to generate and submit them, any platform-specific caching (such as WP Engine's Object Caching or some random plugin) will kick in. The data will also be stored in WordPress' internal cache for the duration of the request, so will not need to be retrieved from the database again, if needed. Jun 2, 2017 at 15:33
  • 1
    Any filters will also be applied to the data, which could be extremely important on, for example, a multi-lingual site. Lastly, since it's using just standard WordPress core functions, it's much less likely to be broken by a future update. Jun 2, 2017 at 15:34
  • 1
    This might be made more performant by limiting the query to post id? Add: 'fields' => 'ids' So, the query array would look like: array( 'post_type' => $post_type, 'meta_key' => $meta_key, 'posts_per_page' => -1, 'fields' => 'ids' )
    – Pea
    Apr 13, 2020 at 18:31
  • 1
    Caution this also filters out meta values that exist only on not published posts so make sure you use the 'post_status' arg to make this a feature not a bug Jul 23, 2020 at 22:25
14

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.

1
  • 1
    I can imagine cases where it would be valid to have multiple meta values of the same value, and thus didn't not make that addition to my code. If you want distinct values, this would be the way to go though. Additionally you could also add that in as an argument for the function(so you can use it or not, as appropriate).
    – t31os
    Jan 24, 2014 at 18:19
6

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'" );
2
  • 3
    The issue with this approach is the lack of specificity, you'll get numerous results from such a query, which could include drafts, trashed items, posts, pages, and any other post type that exists. You should never query for what you don't need, specificity is most certainly required here.
    – t31os
    Jan 24, 2014 at 18:23
  • While it is true that you could get values from other post types and statuses, there are times when all you need are the values and you haven't used that meta_key anywhere but where you need it. If all/most values are unique, this may be the best solution. Jun 23, 2018 at 18:36
4

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.

3
  • 1
    thanks, but shouldn't custom quesries be avoided 'at all cost'? I'd prefer to use the WP abstraction layer (is that what it's called?)... but of course if this is not possible.. Jun 8, 2011 at 10:53
  • 1
    Custom queries, if written the right way, can be better and you should only avoid them if you don't know what you're doing.
    – Bainternet
    Jun 8, 2011 at 13:07
  • 1
    I Agree with mwb .custom queries are very usefull and practical, but I think they are also much heavier on the DB.. especially using SRT functions..
    – krembo99
    Dec 10, 2011 at 3:20
2

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)
1

Use the following with foreach

 $key = get_post_custom_values( 'key' );

Assumes the named of your custom field key is

2
  • Note that this defaults to the current post, when no post_id is specified.
    – birgire
    Jul 29, 2017 at 11:23
  • 1
    This just returns all custom fields for a single post, which by default is the one with ID "0". Note that the documentation explicitly mentions that "the parameters must not be considered optional".
    – Husky
    Feb 24, 2022 at 13:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.