Normally I like to shy away from recommending direct SQL, but in your use-case I think it is warranted.
I’ve coded this up as a direct SQL query; as you may notice is a bit complex. You can copy the following code to your theme’s functions.php
file or add it to the .php
file of a plugin you might be writing:
function home_cats($max_image_height=150){
$categories= get_taxonomy_latest_posts_with_attachment('category');
echo '<h1>Categories</h1>';
foreach($categories as $category) {
$category_link = get_category_link( $category->term_id );
$category_title = sprintf( __( "View all posts with category: '%s'" ),$category->term_name );
$post_title = get_the_title($category->post_id);
$post_link = get_permalink($category->post_id);
$img_html = wp_get_attachment_image( $category->attachment_id, array( 'thumbnail',$max_image_height ) );
$html = <<<HTML
<div id="category-{$category->term_slug}" class="category">
<span style="float:right;">{$img_html}</span>
<span style="float:left;">
<h2><a href="https://wordpress.stackexchange.com/questions/7514/{$category_link}" title="{$category_title}">{$category->term_name}</a></h2>
<p style="float:left;">Latest post: <a href="{$post_link}" title="{$post_title}">$post_title</a></p>
</span>
</div>
<br clear="both" />
HTML;
echo $html;
}
}
function get_taxonomy_latest_posts_with_attachment($taxonomy) {
global $wpdb;
$sql =<<<SQL
SELECT
categorized_posts.rownum,
categorized_posts.term_id,
categorized_posts.term_name,
categorized_posts.term_slug,
categorized_posts.post_id,
categorized_posts.post_date,
categorized_posts.post_title,
attachments.ID AS attachment_id,
attachments.post_title AS attachment_title,
attachments.post_mime_type AS attachment_mime_type,
attachments.guid AS attachment_guid
FROM
(
SELECT
rownum,
term_id,
term_name,
term_slug,
post_id,
post_date,
post_title,
post_parent,
post_name,
post_type
FROM (
SELECT
IF( @prev <> {$wpdb->terms}.term_id, @rownum := 1, @rownum := @rownum+1 ) AS rownum,
@prev := {$wpdb->terms}.term_id,
{$wpdb->terms}.term_id,
{$wpdb->terms}.name AS term_name,
{$wpdb->terms}.slug AS term_slug,
{$wpdb->posts}.ID as post_id,
{$wpdb->posts}.post_date,
{$wpdb->posts}.post_title,
{$wpdb->posts}.post_parent,
{$wpdb->posts}.post_name,
{$wpdb->posts}.post_type
FROM
{$wpdb->term_taxonomy}
INNER JOIN {$wpdb->terms} ON {$wpdb->term_taxonomy}.term_id={$wpdb->terms}.term_id
INNER JOIN {$wpdb->term_relationships} ON {$wpdb->term_relationships}.term_taxonomy_id={$wpdb->term_taxonomy}.term_taxonomy_id
INNER JOIN {$wpdb->posts} ON {$wpdb->posts}.ID={$wpdb->term_relationships}.object_id
INNER JOIN (SELECT @rownum := NULL, @prev := 0) AS rownum_initializer ON 1=1
WHERE 1=1
AND {$wpdb->posts}.post_type="post"
AND {$wpdb->posts}.post_status="publish"
AND {$wpdb->term_taxonomy}.taxonomy='%s'
ORDER BY {$wpdb->posts}.post_parent DESC, {$wpdb->posts}.post_date DESC
) x
) categorized_posts
INNER JOIN (SELECT MAX(ID) AS post_id,post_parent FROM {$wpdb->posts} WHERE post_type="attachment" GROUP BY post_parent) attachment_join ON attachment_join.post_parent=categorized_posts.post_id
INNER JOIN {$wpdb->posts} attachments ON attachments.ID=attachment_join.post_id
WHERE
categorized_posts.rownum=1
GROUP BY
categorized_posts.term_id
ORDER BY
categorized_posts.term_name
SQL;
return $wpdb->get_results($wpdb->prepare($sql,$taxonomy));
}
You’ll note I separated the logic so that you can get the list of any taxonomy terms and their latests posts with a photo by calling the get_taxonomy_latest_posts_with_attachment()
function and passing it a taxonomy identifier, like this:
$post_tags = get_taxonomy_latest_posts_with_attachment('post_tags');
Because of the complexity of the SQL in that function I’m not going to try to explain it (or I’d be here all night) but if you have specific follow up questions, just ask. Anyway, here’s what the code looks like on my test side with test data:
(source: mikeschinkel.com)
P.S. The people in the photos are friends of mine and all work with WordPress in one way or another. Hope they don’t mind me using their likeness. 🙂