Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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

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?

share|improve this question
1  
It's not about a duplicates - it's about retrieving galleries. Did you search? What was close, what didn't work? We expect users to do some research effort before posting a question. –  kaiser Sep 24 '12 at 22:53
    
Well, it doesn't fit the WP_Query parameters, so I'm going to need to use some SQL. The query is pretty well defined in the question, but I can't wrap my head around it to fit it to the SQL syntax. (If I could, I wouldn't need to ask.) –  Just Thomas Misund Sep 25 '12 at 8:38
add comment

1 Answer

up vote 4 down vote accepted

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.

    }
}
share|improve this answer
    
That's a brilliant query. Thank you! I edited a couple of typos and added indentation. –  Just Thomas Misund Sep 25 '12 at 8:45
1  
It occurs to me the HAVING COUNT(ID) > %d should probably be >= –  Nick Q. Sep 25 '12 at 16:09
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.