Sometimes, you just need to get a list of posts, that contain a specific piece of metadata and output them as a list. Instead of getting a list of each post and then looping over all the posts and checking for metadata, it’s a relatively simple SQL statement.

If you had a list of product custom post types and you wanted to find all the entries that had the field name color along with their slug you’d do that by hopping into SQL ( with something like phpMyAdmin ) and pasting this into the Query box:

SELECT p.id, p.post_title p.`post_name` AS slug, m.color, p.post_type
FROM ( 	SELECT post_id AS id, meta_value AS 'color' 
	FROM wp_postmeta
	WHERE meta_key = 'color' ) as m
INNER JOIN wp_posts AS p ON p.id = m.id
WHERE p.post_type = 'product';

You’d end up with something like this:

id	post_title	slug	color	post_type
4	Blue Widget	blue-widget	blue	product
23	Some Gadget	some-gadget	orange	product
123	Another Product	another-product	black	product

Just replace color with your field name and product with your post_type ( like posts if you’re just using blog posts ).

The absolutely bare amount of SQL you’d need just to get a list of colors and titles is this:

SELECT ID, post_title, m.meta_value
FROM ( 	SELECT post_id, meta_value  
	FROM wp_postmeta
	WHERE meta_key = 'color' ) as m
INNER JOIN wp_posts ON id = m.post_id
WHERE post_type = 'product';

Translating this into WordPress SQL

PSA: Use a parameterized query to avoid SQL injection attacks.

To do this in PHP for WordPress (and only get published entries), you’d make a few alterations. These changes will ensure that the SQL will work with whatever database tables YOUR version of WordPress is using and should also work with WordPress Multisite.

Note that you’ll want to take care if you’re using any user input to determine things like which post_type to get, or which field to filter by as yo can leave yourself open to SQL injection attacks. A Classic XKCD highlights this problem of not sanitizing user inputs!

$sql = 
"SELECT ID, post_title, m.meta_value 
FROM ( SELECT post_id, meta_value  
	FROM $wpdb->postmeta
	WHERE meta_key = 'color' ) as m
INNER JOIN $wpdb->posts ON ID = post_id 
WHERE post_status = 'publish' 
AND post_type = 'product'";

results = $wpdb->get_results( $wpdb->prepare( $sql ) );

That should be pretty self-explanatory. Essentially the $wpdb object means you don’t have to worry about database configuration or table names changing for your instance of WordPress. This is especially true with shared hosting. The prepare function does all the magic of converting parameters to proper SQL syntax and protects you from SQL Injection.

Grouping by Meta value

Let’s change the scenario a little bit. Let’s say you wanted to find out how many different colors you had and get a list of how many products are in each color?. You COULD do a simple query on the postmeta table like SELECT meta_value FROM wp_postmeta WHERE meta_key = 'color' GROUP by 1 which may work, but what if you have used the meta_key color for other post types. Maybe you have several post_types like Car and Book etc. Or what if a plugin has also used that key and you don’t know about it?

The safest way to approach the problem is still a simple subquery, so you can still access the data in both wp_posts and wp_postmeta.

$sql = 
"SELECT m.meta_value, COUNT(ID) as amount 
FROM ( SELECT post_id, meta_value  
	FROM $wpdb->postmeta
	WHERE meta_key = 'color' ) as m
INNER JOIN $wpdb->posts ON ID = post_id 
WHERE post_status = 'publish' 
AND post_type = 'product'
GROUP BY m.meta_value";

You should get a nice resultset of colors and numbers:

meta_value	amount
red	76
green	44
blue	21
chartreuse	42

Something a little SQL goes a long way.