Show the last post by date from a custom taxonomy in the WordPress loop

Question

I have a custom taxonomy which I use to bundle posts in series (a series is a term in that taxonomy). What I would like is to treat those bundled posts as special cases when executing the WordPress loop.

Only the first post in the series (the one with the most recent date) should display and all other posts not belonging to a term in the custom taxonomy should be treated as normal posts. Apart from the custom series taxonomy, the posts can also be tagged or categorised (so a maximum of three taxonomies, including the custom one). The loop should contain a fixed number of posts (e.g. the number of posts on the front page set in the WordPress backend).

I'm at a loss on how to group these posts, partly because all the data is in different tables and because each entry can belong to multiple taxonomies. Of course looping over the posts array, discarding the older posts in the series after the MySQL call is a possibility, but in that case the fixed number of posts is hard to maintain without making extra queries to the database.

So I would like to implement a pure SQL solution. I've been experimenting using the posts_clauses hook. This query below returns the post with the highest ID, not the date, in a series and is rather taxing on the database, I believe.

$clauses['fields'] .= ", $wpdb->posts.ID AS postID, (SELECT $wpdb->terms.term_id 
   FROM $wpdb->posts, $wpdb->term_taxonomy, $wpdb->term_relationships, $wpdb->terms     
   WHERE $wpdb->posts.ID=postID 
     AND $wpdb->term_taxonomy.taxonomy='post-series' 
     AND $wpdb->term_relationships.term_taxonomy_id=$wpdb->term_taxonomy.term_taxonomy_id 
     AND $wpdb->posts.ID=$wpdb->term_relationships.object_id 
     AND $wpdb->terms.term_id=$wpdb->term_taxonomy.term_id 
   ORDER BY $wpdb->posts.post_date DESC 
   LIMIT 0,1) AS uniqueID";

$clauses['groupby'] = "IFNULL(uniqueID,$wpdb->posts.ID)";

Based on the clauses above WordPress constructs the following SQL query:

SELECT SQL_CALC_FOUND_ROWS  wp_posts.*, wp_posts.ID AS postID, 
  (SELECT wp_terms.term_id FROM wp_posts, wp_term_taxonomy, wp_term_relationships, wp_terms 
    WHERE wp_posts.ID=postID 
      AND wp_term_taxonomy.taxonomy='post-bundles'
      AND wp_term_relationships.term_taxonomy_id=wp_term_taxonomy.term_taxonomy_id 
      AND wp_posts.ID=wp_term_relationships.object_id 
      AND wp_terms.term_id=wp_term_taxonomy.term_id 
    ORDER BY wp_posts.post_date DESC LIMIT 0,1) 
  AS uniqueID FROM wp_posts 
WHERE 1=1
AND wp_posts.post_type = 'post' 
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') 
GROUP BY IFNULL(uniqueID,wp_posts.ID) 
ORDER BY wp_posts.post_date DESC 
LIMIT 0, 10

I've also read this tutorial, but I'm not sure it applies in this case.

So to summarise: In the loop I only want to show the latest post (ordered by post_date) belonging to a term from a custom taxonomy and exclude other posts in that taxonomy in the loop. The loop also contains regular posts not part of the custom taxonomy and both types may belong to multiple categories and have tags.

Answer 1

Ok here is my answer ( it's more a suggestion)

My aproach would be first select every id from the posts that's the most recent in their term and belongs to the taxonomy post-series.

select * from wp_posts where ID in(
select ID from
(
select wp_posts.ID , wp_posts.post_date, d.name from wp_posts as a
join wp_term_relationships as b on ( a.ID = b.object_id)
join wp_term_taxonomy as c on (b.term_taxonomy_id = c.term_taxonomy_id)
join  wp_terms as d on (c.term_id = d.term_id)
where c.taxonomy = 'post-series'
group by d.name 
having (wp_posts.post_date = max(wp_posts.post_date))
)tmp)

i grouped by d.name because you want one entry for that name and use having because you want the one entry that has the max post_date

then i select all the id's that come up from that query and use them in the where ID in () clause

I couldn't test this but this is how i would solve it in mysql.