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

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

"When the answer escapes you, change the question..."

I just released a plugin that enables bulk selection of a “featured post image”, directly from the media library. The core of my function went from ~45 lines to this;

foreach($potential_parents as $parent){
   if(has_post_thumbnail($parent->ID)) {
      $image = get_post_thumbnail_id($parent->ID);
   }
}

It's not random but the site looks better and visitors have an easier time navigating content now that the thumbnails are consistent.

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.

I solved it! Re-discovered subqueries and the IN-keyword today. :) Seems plenty fast and correct to boot.

$images = $wpdb->get_results(
    "SELECT SQL_SMALL_RESULT DISTINCT wp_posts.post_parent, wp_posts.ID 
    FROM wp_posts 
    WHERE wp_posts.post_parent IN (
        SELECT SQL_SMALL_RESULT DISTINCT wp_posts.ID
        FROM wp_posts       
            LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) 
            LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) 
        WHERE 
            wp_term_taxonomy.taxonomy = 'category' 
        AND wp_term_taxonomy.term_id IN({$wpdb->escape($category_filter)}) 
        AND wp_posts.post_type = 'post' 
        AND wp_posts.post_status = 'publish' 
        AND wp_posts.post_password = ''             
        ORDER BY {$order_by}
    )       
    AND wp_posts.post_type = 'attachment' 
    AND wp_posts.post_mime_type IN ('image/jpeg', 'image/gif', 'image/png') 
    ORDER BY {$order_by} 
    LIMIT {$limit};"
);
$selection = array();                           
foreach($images as $img){
    $imgurl = wp_get_attachment_image_src($img->ID, $size); 
    if($imgurl === false){continue;} //the image doesn't exist? 
    $img_width = $imgurl[1];
    $img_height = $imgurl[2];
    $imgurl = $imgurl[0];       
    $selection[] = array(
        'post_url' => get_permalink($img->post_parent), 
        'post_title' => wp_specialchars(get_the_title($img->post_parent)),
        'post_id' => $img->post_parent, 
        'img_id' => $img->ID, 
        'img_src' => $imgurl, 
        'width' => $img_width, 
        'height' => $img_height
    );                      
}
return $selection;

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){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. :)