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:
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
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
$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:
Something a little SQL goes a long way.