Created
September 9, 2015 13:15
-
-
Save anonymous/38a56d1749a9982e6b5a to your computer and use it in GitHub Desktop.
How to find Wordpress featured images together with respective post directly from DB query (for imports, migrations etc.)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT p.* | |
FROM wp_postmeta AS pm | |
INNER JOIN wp_posts AS p ON pm.meta_value=p.ID | |
INNER JOIN wp_posts AS parent ON p.post_parent = parent.`ID` | |
WHERE pm.meta_key = '_thumbnail_id' | |
AND parent.post_status = 'publish' | |
/* You can add more conditions for posts like post type etc. */ | |
ORDER BY p.post_date DESC |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Much better:
SELECT parent.*, f.meta_value AS file
FROM wp_postmeta AS pm
INNER JOIN wp_posts AS p ON pm.meta_value=p.ID
INNER JOIN wp_posts AS parent ON p.post_parent = parent.
ID
INNER JOIN wp_postmeta AS f ON p.ID = f.post_id AND f.meta_key = '_wp_attached_file'
WHERE pm.meta_key = '_thumbnail_id'
AND parent.post_status = 'publish'
ORDER BY p.post_date DESC