Useful Snippets

Welcome!


This blog is used to collect useful snippets related to Linux, PHP, MySQL and more. Feel free to post comments with improvements or questions!

RSS Latest posts from my personal blog


Most viewed posts


Subscribe to RSS feed


Get all attachments whose post parent is published in WordPress

Stanislav KhromovStanislav Khromov

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

PHP

Web Developer at Aftonbladet (Schibsted Media Group)
Any opinions on this blog are my own and do not reflect the views of my employer.
LinkedIn
Twitter
WordPress.org Profile
Visit my other blog

Comments 2
  • gurung
    Posted on

    gurung gurung

    Reply Author

    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.


    • Stanislav Khromov
      Posted on

      Stanislav Khromov Stanislav Khromov

      Reply 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.