Reading the post meta of many posts with one single query.

Sometimes you need to read the post meta of many posts, but WordPress gives you only the inbuilt function get_post_meta.

So usually you do something like this:

$meta_key = '_example_meta_key';
foreach( $posts as $post ){
    $value = get_post_meta( $post->ID,$meta_key,true );
}

 

Inside the foreach cycle, you usually do something with the value you get from that post.

Doing so, at every cycle interaction you are making a database query. If you have many posts, you will have many queries.

If you call get_post_meta always on the same post, you will have only one query.

I mean, WordPress will make only one single database query  if you do something like this:

 

$value1 = get_post_meta( $post->ID,$meta_key1,true );

$value2 = get_post_meta( $post->ID,$meta_key2,true );

$value3 = get_post_meta( $post->ID,$meta_key3,true );

 

 

This is because the first time you call get_post_meta, WordPress stores in the cache the results of the post meta of that post.

But the situation is totally different if you are asking the post meta of different posts. Is it still possible to fetch the results with one single query? Of course, it is.

Here you have a function to retrieve the post meta given the meta key name and the array of post IDs.

 

//It retrieves multiple metadata given the $meta_key and the array of post IDs
function jose_get_multiple_metadata( $meta_key,$ids ){
    if( empty( $ids ) || '' === $meta_key ) return false;
        global $wpdb;
        if( is_array( $ids ) ){
            $ids = implode( ',',array_map( 'absint',$ids ) );
        }
        elseif( is_string( $ids ) ){
            $ids = implode( ',',array_map( 'absint',explode( ',',$ids ) ) );
        }
        $ids = esc_sql( $ids );
        $meta_key = esc_sql( $meta_key );
        $sql = "SELECT post_id,meta_value FROM $wpdb->postmeta WHERE post_id IN ($ids) AND meta_key='$meta_key';";
        return $wpdb->get_results( $sql,OBJECT );
}

 

 

As you see we build an SQL query to fetch data from the table wp_postmeta, looking in the rows with the IDs specified by the argument $ids and having the meta key specified in the argument $meta_key, and getting the values of the column meta_value.

Especially if you have many posts, the performance of this method will be dramatically higher than calling get_post_meta for every single post.

Here an example of the use of the function above:

 

 

$ids = array( 234,325,436,424 );

$meta_valuesObj =jose_get_multiple_metadata( '_example_meta_key',$ids );
foreach( $meta_valuesObj as $obj ){
    $meta_value= $obj->meta_value;

    //Do something with the meta value
}

 

In the example above we still have a foreach cycle, but inside the cycle, we have no database queries.

 

 

All categories