How can I query posts with newly uploaded images?

1 Views

I have a custom post type for events. A couple of days after each event photos are sometimes uploaded and attached to the event they were taken at.

I want to highlight these image gallerys somewhere on my site, displaying them under a headline “Recently Changed Image Galleries”.

Read More

How can I query posts of type ‘event’ that have 3 or more image attachments, and order the result by the upload or publish time of the newest attachment?

Post Views: 1

Related posts

Leave a Reply

1 comment

  1. This should do it for you.

    If you’re calling it in a function, you’ll of course need to ensure $wpdb is the global. 

    $required_number_of_attatchments = 3;

$posts = $wpdb->get_col( 
    $wpdb->prepare( 
        "SELECT posts.ID
            FROM %s AS posts
            INNER JOIN (
                SELECT MAX(post_modified) AS modified, post_parent
                FROM %s
                WHERE post_type = 'attachment'
                AND post_parent > 0
                GROUP BY post_parent
                HAVING COUNT(ID) > %d
            ) AS attachments
            ON posts.ID = attachments.post_parent
            WHERE posts.post_type = 'event'
            ORDER BY attachments.modified DESC",
        $wpdb->posts,
        $wpdb->posts,
        $required_number_of_attatchments
    )
);

if ( $posts ) {
    foreach ($posts as $post_id) {
        $post = get_post( $post_id );
        setup_postdata( $post );

        // Do your thing.

    }
}