Single query for selecting a random images from X unique parents?

Question

I've used this query to randomly select a total of $limit-images (attachments), each from a unique and randomly selected parent.

$query="SELECT {$wpdb->posts}.post_parent, {$wpdb->posts}.ID
                    FROM {$wpdb->posts}                    
                    INNER JOIN {$wpdb->term_relationships} ON ({$wpdb->posts}.post_parent = {$wpdb->term_relationships}.object_id)
                    INNER JOIN {$wpdb->term_taxonomy} ON ({$wpdb->term_relationships}.term_taxonomy_id = {$wpdb->term_taxonomy}.term_taxonomy_id)
                    WHERE {$wpdb->posts}.post_type = 'attachment'
                    AND {$wpdb->term_taxonomy}.taxonomy = 'category' AND {$wpdb->term_taxonomy}.term_id IN ('{$wpdb->escape($category_filter)}')
                    AND {$wpdb->posts}.post_password = ''
                    AND {$wpdb->posts}.post_mime_type IN ('image/jpeg', 'image/gif', 'image/png')                                                  
                    GROUP BY {$wpdb->posts}.post_parent
                    ORDER BY {$order_by}                                   
                    LIMIT {$limit};";

(full code at pastebin)

Unfortunately it has three faults:

1. I think the password-check is incorrect as it tests the attachment and not the parent_post, right? (does WordPress even support password protected gallery attachments?)

2. it certainly doesn't check the parent for post_status = "publish"

3. it correctly selects random posts, but always the same pictures within them (the first one).

So - I humbly ask for your SQL-fu. How does one both select a random parent (first checking for published status) and then a random image ID owned by that parent, all in one query?

(I could select all attachments, ordered randomly and loop through them all and just grab the first $limitfrom unique parents. But that leads to parents with lots of images getting selected too often.)

Answer 1

Okay. So here's one way to do it, using WordPress' API. It's very clumsy and very slow, but appears correct. I would of course much rather let MySQL do all this work.

$count = $limit;
$parent_posts = get_posts(array(
    'post_type' => 'post',
    'numberposts' => $limit,
    'post_status' => 'publish',
    'category' => $category_filter,
    'orderby' => $order_by
)); 
foreach ($parent_posts as $parent_post) {
    $attachments = get_posts(array(
        'post_parent' => $parent_post->ID,
        'post_mime_type' => '"image/jpeg", "image/gif", "image/png"', //Not sure if this is functional http://wordpress.org/support/topic/361633
        'post_type' => 'attachment',
        'numberposts' => 1,
        'post_status' => 'inherit',
        'orderby' => 'rand'
    ));
    foreach($attachments as $attachment){ //NOTE: $attachments might be empty
        $imgurl = wp_get_attachment_image_src($attachment->ID, $size); 
        if($imgurl === false){continue;} //bail, don't add to selection, don't decrease $count  
        /*[... do whatever with the image, add it to $selection ...]*/  
        if(--$count < 1){return $selection;}
    }
}

Note that you have to wrap this in a while-loop to make sure $selection gets filled. Some potential $parent_posts might not have any children.

Answer 2

It's not one query, but it's a heck of a lot faster than the WordPress-hackery I posted earlier, and not very much slower than the original. I suppose it's the price to pay for correctness and while being SQL-ignorant. :P

$potential_parents = $wpdb->get_results(
        "SELECT DISTINCT {$wpdb->posts}.ID, {$wpdb->posts}.post_title 
        FROM {$wpdb->posts} 
            LEFT JOIN $wpdb->postmeta wpostmeta ON ({$wpdb->posts}.ID = wpostmeta.post_id) 
            LEFT JOIN $wpdb->term_relationships ON ({$wpdb->posts}.ID = $wpdb->term_relationships.object_id) 
            LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id) 
        WHERE $wpdb->term_taxonomy.taxonomy = 'category' AND $wpdb->term_taxonomy.term_id IN({$wpdb->escape($category_filter)}) 
        AND {$wpdb->posts}.post_type = 'post' 
        AND {$wpdb->posts}.post_status LIKE 'publish' 
        AND {$wpdb->posts}.post_password = ''           
        ORDER BY {$order_by};");
$imglists = array();
$parent_titles = array();
$count = $limit;
foreach($potential_parents as $parent){
    $images = $wpdb->get_results(
        "SELECT {$wpdb->posts}.ID  
        FROM {$wpdb->posts} 
        WHERE {$wpdb->posts}.post_parent = {$parent->ID}
        AND {$wpdb->posts}.post_type = 'attachment' 
        AND {$wpdb->posts}.post_mime_type IN ('image/jpeg', 'image/gif', 'image/png') 
        ORDER BY {$order_by} 
        LIMIT 1;"); 
    if($images){
        $imglists[$parent->ID] = $images;
        $parent_titles[$parent->ID] = $parent->post_title;
        if(--$count < 1){break;}
    }       
}   
foreach($imglists as $parent_id => $imagelist){
    $imgurl = wp_get_attachment_image_src($imagelist[0]->ID, $size); 
    if($imgurl === false){echo '<!-- invalid img selection -->'; continue;} //the image doesn't exist?  
    $img_width = $imgurl[1];
    $img_height = $imgurl[2];
    $imgurl = $imgurl[0];       
    $selection[] = array('post_url'=>get_permalink( $parent_id ), 'post_title' => wp_specialchars($parent_titles[$parent_id]),'post_id'=>$parent_id, 'img_src'=>$imgurl, 'width'=>$img_width, 'height'=>$img_height);                   
}
return $selection;

So basically, first grab one largish result set with all published posts in the categories. Then a bunch of smaller queries, fetching a single attachment ID every loop until the $limit has been filled.

If you've got lots of posts in these categories without attachments, you'll waste some time here. But in our case it seems to be manageable.

Still looking for that efficient single-query solution though. :)