Selecting WordPress Metadata – Database Journal



In general terms, the word “Metadata” refers to data about data.  In MySQL, anything that describes the database—as opposed to the actual contents of the database—is metadata.  In WordPress (WP), the meaning of Metadata is a bit fuzzier.  It stores post metadata such as custom fields and additional metadata added via plugins in the wp_postmeta table.  In terms of blog content, just about anything other than the main post content can be considered metadata.  Therefore, it pays to get a solid handle on how metadata is stored and retrieved in WP.  In today’s article, we’ll learn how to fetch metadata from the wp_postmeta table using the versatile $wpdb object.

More on WP Metadata

Before we start writing queries to get at WP metadata, let’s take a closer look at some of the three main types of metadata:

  • Custom Fields. These apply to posts and are stored in the wp_postmeta table. You can add them programmatically using the standard Custom Fields interface (see the next section) or by creating your own metaboxes in the post editing screens.
  • User Metadata. The wp_usermeta table stores metadata about most users, such as roles, capabilities, dashboard settings and info such as first and last names.
  • Metadata added by plugins and themes. Plugins with comment functionality such as Akismet will add metadata to the wp_commentmeta table, while plugins that allow you to add metadata to posts, such as SEO plugins, will add records to the wp_postmeta table. (You could technically add metadata via your theme but it makes more sense to do this in a plugin, as it is generally related to functionality and not display.)

Of course the list isn’t limited to these types. In fact, any data about posts, comments or users which can’t be stored in their main database tables will be stored in the relevant metadata tables. 

If you’re adding a new field to store data, you should always use these tables and not the core tables.

The Metadata API

I would be remiss if I didn’t mention the WP Metadata API.  It’s a set of functions for inserting, updating, getting, and deleting Metadata from the three Metadata tables.  They follow a simple naming convention whereby:

  1. Tables are named “post”, “user”, and “comment”.
  2. Operations are named “add”, “update”, “get”, and “delete”.

Hence, the four functions for the wp_postmeta table are:

add_post_meta() get_post_meta() update_post_meta() delete_post_meta()

For the Users table they are:

add_user_meta() get_user_meta() update_user_meta() delete_user_meta()

You get the picture.

In the case of get_ functions, these can return one value or all values for a post, depending on the arguments passed to it.  

The limitation of the get_ functions is that they require a specific key value to work with.  In that sense they are an all-or-nothing type proposition; you either have a specific Meta value in mind, or you want all of them.

A Case for $wpdb: Using the LIKE Operator

A common requirement is to fetch Metadata whose keys start with a specific substring.  Those of you who are familiar enough with SQL will immediately recognize this as a candidate for the LIKE operator.  Here is some PHP code that retrieves all of the values from the wp_postmeta table whose keys begin with the phrase “movie_name” and sorts them in ascending order:

global $wpdb;
 
$querystr = "
    SELECT DISTINCT meta_value     FROM $wpdb->postmeta     WHERE meta_key LIKE 'movie_name'     ORDER BY meta_value ASC ";
 
$movie_names = $wpdb->get_results( $querystr, OBJECT );
 
if ( ! $movie_names ) {
    $wpdb->print_error();
}
else {
    // Do something awesome with the movies
}
 

About Metadata Types

You can store just about any type of data in the wp_postmeta table, but be aware that, behind the scenes, all meta_values are stored as strings.  That can have implications on how you treat metadata within your queries.

For instance, say that the metadata is stored as a number.  If you wanted to fetch posts whose ‘category’ meta_value is greater than 10, you should cast it as a number before testing it:

SELECT
wp_posts.* FROM   wp_posts p
INNER JOIN wp_postmeta pm
  ON (p.ID = pm.post_id) WHERE p.post_type = 'product' AND (p.post_status = 'publish' OR p.post_status = 'private')
AND (pm.meta_key = 'category' AND CAST(pm.meta_value AS UNSIGNED) > 10)
GROUP BY p.ID ORDER BY p.post_date DESC;

Querying on Multiple Metadata

Due to the way that each piece of metadata is stored as a separate row in the wp_postmeta table, in order to query on multiple Meta keys and/or value, it is necessary to create an additional table reference for each.

To illustrate, here is a SELECT statement that references two pieces of Metadata.  For each of these:

  1. The wp_postmeta table is joined to the wp_posts table via an INNER JOIN.
  2. In the WHERE clause, the meta_key and meta_value are referenced for matching against.
SELECT *
FROM wp_posts
INNER JOIN wp_postmeta m1
  ON (wp_posts.ID = m1.post_id )
INNER JOIN wp_postmeta m2
  ON (wp_posts.ID = m2.post_id )
WHERE wp_posts.post_type   = 'post'
AND   wp_posts.post_status = 'publish'
AND ( m1.meta_key = 'date' AND m1.meta_value > '2010-12-05 00:00:00' )
AND ( m1.meta_key = 'date' AND m1.meta_value < '2017-01-01 00:00:00' )
AND ( m2.meta_key = 'some_other_meta_value' AND m2.meta_value != '' )
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date
DESC;

Querying on Serialized Metadata

As stated above, you can store just about any type of data in the wp_postmeta table.  That includes scalar types such as strings, integers, and dates, as well as arrays.

Under the covers WordPress employ’s the serialize() PHP function to store arrays as a serialized string.  In the database, the meta_value might look something like this:

a:8:{i:0;s:31:"query-monitor/query-monitor.php";i:1;s:57:"accesspress-instagram-feed/accesspress-instagram-feed.php";i:2;s:19:"akismet/akismet.php";i:3;s:29:"
easy-captcha/easy-captcha.php";i:4;s:43:"google-analytics-dashboard-for-wp/gadwp.php";i:5;s:33:"instagram-feed/instagram-feed.php";i:6;s:19:"jetpack/jetpack.php";i:7;s:47: "really-simple-captcha/really-simple-captcha.php";}

When WordPress fetches the data, it converts it to something that resembles this:

$array = array(
 '0' => 'query-monitor/query-monitor.php'
 '1' => 'accesspress-instagram-feed/accesspress-instagram-feed.php'
 '2' => 'akismet/akismet.php'
 '3' => 'easy-captcha/easy-captcha.php'
 '4' => 'google-analytics-dashboard-for-wp/gadwp.php'
 '5' => 'instagram-feed/instagram-feed.php'
 '6' => 'jetpack/jetpack.php'
 '7' => 'really-simple-captcha/really-simple-captcha.php'
 );

For that reason, it’s best to use the WP_Query() object to fetch serialized data.  You can then work with individual values within a while loop.  The following code iterates over each metadata row and converts them to separate Metadata fields:

$args = array(
    'post_type' => 'my-post-type',
    'meta_key' => '_coordinates',
    'posts_per_page' => -1
 ); $query = new WP_Query( $args ); if($query->have_posts()){
    while($query->have_posts()){
        $query->the_post();
        $c = get_post_meta($post->id,'_coordinates',true);
        add_post_meta($post->ID,'_longitude',$c['longitude']);
        add_post_meta($post->ID,'_latitude',$c['latitude']);
        delete_post_meta($post->ID,'_coordinates',$c);
    }
}

You can also use the third parameter of get_post_meta() to return serialized data as an array.

If you know the structure of the serial string, it is possible to search for a specific value in the string such as ‘;s:6:”weight’. You can then return the next 10 characters such as an “xx/xx/xxxx”-formatted date.

SELECT wp_posts.ID
      ,MID(
        CAST(wp_postmeta.meta_value AS CHAR),
        POSITION(';s:6:\"weight' IN CAST(_postmeta.meta_value AS CHAR) ),
        10
      ) AS dres  FROM wp_posts p
INNER JOIN wp_postmeta pm ON (p.ID = pm.post_id)
WHERE p.post_type   = 'dog' AND   p.post_status = 'publish'
AND   pm.meta_key   = '_meta_general'
AND POSITION(';s:6:\"weight' IN CAST(pm.meta_value AS CHAR)) > 0
GROUP BY dres ORDER BY pm.meta_value ASC LIMIT 0, 10;

Conclusion

Between the WP Metadata API, WP_Query(), and the versatile $wpdb object, there is no shortage of ways to retrieve Metadata from your WP database(s).  The method that you select should be based on your specific requirements at that time.

See all articles by Rob Gravelle



こんな記事も読まれています



コメントを残す