Sort X categories by last update and show image

Here’s one solution:

global $wpdb;

$cat_array = $wpdb->get_results( "
    SELECT terms.*, posts.ID as post_ID
    FROM wp_terms terms 
    JOIN wp_term_taxonomy term_taxonomy 
        ON terms.term_id = term_taxonomy.term_id
    JOIN wp_term_relationships term_relationships 
        ON ( term_relationships.term_taxonomy_id = term_taxonomy.term_taxonomy_id 
            AND term_taxonomy.taxonomy = 'category' )
    JOIN wp_posts posts 
        ON ( posts.ID = term_relationships.object_id 
            AND posts.post_type="post"
            AND posts.post_status="publish" )
    GROUP BY terms.term_id
    ORDER BY posts.post_modified_gmt DESC" );

foreach ($cat_array as $cat) {

    $category = get_term_by( 'ID', $cat->term_id, 'category');
    $post = get_post( $cat->post_ID );

    echo '<a href="' . esc_attr(get_term_link($category, 'category')) . '" title="' . sprintf( __( "View all posts in %s" ), $category->name ) . '" ' . '>';
    echo get_the_post_thumbnail( $cat->post_ID, 'post-thumbnail' );
    echo $category->name .': '.get_the_title( $post->ID ).'</a>'.'<br />';

}

The query returns the categories in the order you want, sorted by most recent post, along with the post ID of the most recent post in that category, which you can then use to get the post thumbnail or whatever other data you want from it (I also echoed the post title in my example, just to show how it can be done).

Leave a Comment