Wordpress loves to store data in fields like wp_postmeta, sometimes making it hard to hack into the data you want to extract, usually for a post. When working with Wordpress, the most effective way to manipulate data is not via a plugin or some native Wordpress function, it's with SQL. For now, there's no better tool for exploring or manipulating data (including those "universal import/export" plugins, gimme a break).

The Query

The actual query to extract image URLs from a post is simple:

SELECT pm2.meta_value as 'file_url', p1.ID as 'post_id' 
FROM wp_posts p1 
LEFT JOIN wp_postmeta pm1 ON pm1.post_id = p1.ID AND pm1.meta_key='_thumbnail_id'
LEFT JOIN wp_posts p2 ON p2.ID = pm1.meta_value
LEFT JOIN wp_postmeta pm2 ON pm2.post_id = p2.ID AND pm2.meta_key='_wp_attached_file'

This is because the relation works like this: a thumbnail ID is attached to the post via post_meta (of course, it's always freakin' post_meta). That thumbnail ID references the post of the attachment. In turn, that post has meta data, too...so we need the final join on post_meta again to obtain the attached file.

Joy.

Note that the post ID returned is the post ID for the actual post that has the image attached, not the post ID of the attachment. Confused? Not my fault, I didn't design the wacky WP schemas.

How to Use This

You can wrap this entire statement in a CTE or temporary table if you want, and now you have a simple mapping of post IDs and their attached "featured images":

CREATE TEMPORARY TABLE IF NOT EXISTS featured_images ... (copy/paste same SQL as above)

You can combine this with other queries if you want to easily join this image data with other post meta fields. This is useful if you need a table (temporary or otherwise) with post meta data as columns instead of rows. You often do need the data in this form with Wordpress, so it's a very handy technique.

That's it?

This post was super easy, yeah? We have a simple question, and there's a simple answer! I think it's important to be well versed on the Wordpress schema when working in this ecosystem -- yes, there's all sorts of PHP functions and query abstractions you can use to fetch data...but there's a million reasons why you may need to use SQL, and it's always going to be the best tool for working with WP data.