Fetch posts with attachments in a certain category?
- by TiuTalk
I need to retreive a list of posts that have (at least) one attachment that belongs to a category in WordPress.
The relation between attachments and categories I made by myself using the WordPress default method.
Here's the query that i'm running right now:
SELECT p.*
FROM `wp_posts` AS p # The post
INNER JOIN `wp_posts` AS a # The attachment
ON p.`ID` = a.`post_parent`
AND a.`post_type` = 'attachment'
INNER JOIN `wp_term_relationships` AS ra
ON a.`ID` = ra.`object_id`
AND ra.`term_taxonomy_id` IN (3) # The category ID list
WHERE p.`post_type` = 'post'
ORDER BY p.`post_date` DESC
LIMIT 15
The problem here is that the query only use the first found attachment, and if it doesn't belongs to the category, the result isn't returned.
