Getting WP Post and Postmeta in Single Rows

The Custom Post Type is a great feature for WordPress developers; it allows you to take advantage of many of WordPress’ built-in functions for your own data types. On the WordPress dashboard, you can see your own Custom Post Type (CPT) information displayed through familiar screens, but with your own custom taxonomies and fields.

For a recent custom plugin that we made to help check people in at events, I wanted CPTs for “events” and “patrons.” The patrons are the members of the group for which the plugin was made, and the events are their meetings: event staff will use the plugin to check people in as they come through the door.

WordPress uses wp_posts to store post, page, and CPT data. You get all the normal fields (title, content, excerpt, etc.) and you can turn them off or re-label them for different purposes. Chances are, however, you’ll have some of your own data points to store, and for those, you can use WordPress Custom Metaboxes. As an example, this is my “patron” CPT screen in the WordPress dashboard: (screenshot missing; planning to recreate!)

WordPress stores metabox data in the wp_postmeta table in a key/value format. You could choose to lump these together with something like PHP’s serialize or you can have a separate wp_postmeta row for each key and value. I chose the latter. I’ve done it both ways, and keeping them separate seems more flexible.

To retrieve this data, however, you must read for the post plus multiple linked post meta rows. For instance, the SQL for this might be:

SELECT p.ID, p.post_title, pm.meta_key, pm.meta_value
FROM wp_posts p, wp_postmeta pm
WHERE p.post_type = 'patron' AND p.post_status = 'publish'
AND pm.post_id = p.ID;

This would return rows that look like (for example):

ID     POST_TITLE     META_KEY        META_VALUE
1      'Jane Dough'   'first_name'    'Jane'
1      'Jane Dough'   'last_name'     'Dough'
1      'Jane Dough'   'email'         'jane@email.com'
2      'John Dough'   'first_name'    'John'
2      'John Dough'   'last_name'     'Dough'
2      'John Dough'   'email'         'john@email.com'

When you iterate this data – say with “foreach ($patrons as $patron)” – you’re going to need some code logic to sort it all out. Nothing wrong with that, but why not make it simpler?

We can do that with a great feature of SQL: the subquery.

Subqueries

In MySQL/MariaDB (and almost every version of SQL I’ve encountered) you have the option of making subqueries. These are queries within the query that get evaluated before the main query. Using our “patrons” example, here’s what I did to get the wp_postmeta data to come back with all data points in a single row:

select p.ID, p.post_title,
  (select pm.meta_value from wp_postmeta pm
   where pm.meta_key = '_csbn_patron_first_name_key' and pm.post_id = p.ID) csbn_patron_first_name_key,
  (select pm.meta_value from wp_postmeta pm
   where pm.meta_key = '_csbn_patron_last_name_key' and pm.post_id = p.ID) csbn_patron_last_name_key,
  (select pm.meta_value from wp_postmeta pm
   where pm.meta_key = '_csbn_patron_email_address_key' and pm.post_id = p.ID) csbn_patron_email_address_key
from wp_posts p
where p.post_type = 'cpt_patron' and p.post_status = 'publish'
order by p.post_title

(The “csbn” is my plugin prefix which I used to tag my meta keys to make them distinct.)

So what’s going on here? Lines 2-7 represent three subqueries; I’ve highlighted the first and third to make them stand out. Before we talk about them, however, let’s look at the outer query by itself:

select p.ID, p.post_title,
...
from wp_posts p
where p.post_type = 'cpt_patron' and p.post_status = 'publish'
order by p.post_title

The outer query, with no subqueries, is getting data from my parent row (wp_posts) for its display line, and down in there WHERE it’s specifying my CPT’s name (“cpt_patron” types that have been published). This gets one row each from wp_posts and forms the basis of my result set:

ID     POST_TITLE
1      'Jane Dough'
2      'John Dough'

Where the “…” appears in the above stripped-down query, I inserted my subqueries. A subquery should always be in parentheses, and after the closing paren, we give it the name that will be used in the result set:

...
  (select pm.meta_value from wp_postmeta pm
   where pm.meta_key = '_csbn_patron_first_name_key' and pm.post_id = p.ID) csbn_patron_first_name_key,
...

In each case (all three subqueries are very similar), I have a complete query that retrieves the value (pm.meta_value) of the data point. It draws from wp_postmeta where the key matches one of my predefined custom keys and the wp_postmeta post_id matches the ID of the overall row.

Notice the use of aliases: “p” (wp_post) is part of the parent query, and “pm” (wp_postmeta) only appears in the subqueries. When I refer to “pm” in my subquery, it’s local to the subquery; when I reference “p.ID,” it’s from the parent. Ergo, as we process any given row, we get a post ID, and a post title (both from wp_posts) as well as the three subqueries get their respective meta information to go with it.

In my case, I ended up with results like this: (screenshot missing; I need to make a new one!)

With this result set, my PHP code only has to read and dump to the screen… none of the first-row (per group) processing that I would have needed to handle wp_posts and wp_postmeta data as separate rows.

The Plugin Code

For the sake of (semi!) completeness, here’s the query used in my WordPress plugin’s PHP code:

$patrons = $wpdb->get_results(
	$wpdb->prepare(
		"select distinct p.ID, p.post_title, " .
			"(select pm.meta_value from " . $wpdb->prefix . "postmeta pm " .
			"where pm.meta_key = '_csbn_patron_first_name_key' " .
			"and pm.post_id = p.ID) csbn_patron_first_name_key, " .
			"(select pm.meta_value from " . $wpdb->prefix . "postmeta pm " .
			"where pm.meta_key = '_csbn_patron_last_name_key' " .
			"and pm.post_id = p.ID) csbn_patron_last_name_key, " .
			"(select pm.meta_value from " . $wpdb->prefix . "postmeta pm " .
			"where pm.meta_key = '_csbn_patron_email_address_key' " .
			"and pm.post_id = p.ID) csbn_patron_email_address_key " .
		"from " . $wpdb->prefix . "posts p, " . $wpdb->prefix . "postmeta pm " .
		"where p.post_type = 'cpt_patron' and p.post_status = 'publish'" .
		"and pm.post_id = p.ID and pm.meta_key like '_csbn%'" .
		"order by p.post_title", null
	)
);

foreach ($patrons as $patron) {
    ... do exciting stuff like ...
    $screen .= '<p><button class="csbn_button csbn_button4"><a href="#header">Back to Top</a></button> <button class="csbn_button csbn_button4"><a href="#actions-sidebar">Add New</a></button></p>';
    ...
}

Nothing rocket science here, but hopefully it’s a different way of looking at things. There are a ton of amazing techniques that can be employed on the SQL side, and once done they have a DRYing effect as well making it easier to implement standards.