I want to display X categories, and sort them by last update.
I also want to grab the image of the latest post with the category.
<img src="https://wordpress.stackexchange.com/questions/25860/<?php echo get_image("article_image',1,1,0,NULL,$res75); ?>" />
So, the category list will be displayed by images, with the corresponding category-name underneath.
Sorted by last update.
How would I go about doing that?
2 Answers
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).