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

I'd suggest wrapping it into a function that queries based on key using get_col to select just the meta values, you'll get a nice flat array of data returned(you'll not get that with get_results unfortunately).

function get_meta_values( $key = '', $type = 'post', $status = 'publish' ) {
    global $wpdb;
    if( empty( $key ) )
        return;
    $r = $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_status = '%s' 
        AND p.post_type = '%s'
    ", $key, $status, $type ) );
    return $r;
}

Assuming you're querying for meta values associated with posts that are published selecting all the meta values is a simple case of..

$my_var = get_meta_values( 'YOURKEY' );

Or if you want to see a quick print out of the results(without needing to loop over the array returned)..

echo implode( '<br />', get_meta_values( 'YOURKEY' ));

The above query should be pretty light, and if you want to do counts, you can work that out looping over the returned data, eg..

$my_var = get_meta_values( 'YOURKEY' );
if( !empty( $my_var ) ) {
    $meta_counts = array();
    foreach( $my_var as $meta_value )
        $meta_counts[$meta_value] = ( isset( $meta_counts[$meta_value] ) ) ? $meta_counts[$meta_value] + 1 : 1;
}

$meta_counts would then hold an array of counts, the key is the meta value, the value is the count for that value, ie..

Array(
  [some_meta_value] => 10
  [some_other_value] => 2
)

I hope that helps.. :)

Answer 2

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

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)