Get all attachments whose post parent is published in WordPress

I was building an “image wall” for a WordPress site, consisting of all images attached to posts, and had to pull out all image attachments. It also should not pull in attachments to posts that are not yet published, and it needs to be a single query to allow for pagination. Here is the result:

global $wpdb;

$posts_per_page = 10;
$offset = 0;

$attachment_posts = $wpdb->get_results("
    SELECT
    ID,
    post_parent as parent,
    post_status,
    (SELECT post_status FROM {$wpdb->prefix}posts wp2 WHERE wp2.ID = wp.post_parent AND (post_type = 'post' OR post_type = 'page')) as parent_status,
    (SELECT post_date FROM {$wpdb->prefix}posts wp3 WHERE wp3.ID = wp.post_parent AND (post_type = 'post' OR post_type = 'page')) as parent_date
    FROM {$wpdb->prefix}posts wp
    WHERE post_type = 'attachment'
    AND post_status = 'inherit'
    AND post_parent <> 0
    HAVING parent_status = 'publish'
    ORDER BY parent_date DESC
    LIMIT {$posts_per_page} OFFSET {$offset};",
    ARRAY_A);

foreach($attachment_posts as $attachment_post) { 
    //Loop over attachments. 
}

Additional reading
StackOverflow post

2 thoughts on “Get all attachments whose post parent is published in WordPress

  1. gurung

    Hi, This is great. I need to do something like this too. Is there a non-sql method of doing this ? I have been looking for this everywhere including WPSE. I will bookmark this page. It would be amazing to get a response. Also please throw some light on performance aspect of the code.
    Thnx.

    Reply
    1. Stanislav Khromov Post author

      Hey Gurung,

      There’s no way of doing this just using WP_Query, because WP_Query does not support subqueries. You could do it in a multi-step process, but it would make pagination difficult.

      Performance-wise, I haven’t noticed any issues on blogs with a “normal” amount of posts (<5000) , but I do encourage caching the result of this query in the WordPress Object Cache. Then, you can invalidate it on the save_post action very easily.

      You can run MySQL EXPLAIN to see some info about the indexes being used.

      Hope that helps.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Markdown is allowed in comments.