
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.