title: Show the last post by date from a custom taxonomy in the WordPress loop
body: <p>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.</p>
  
  <p>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).</p>
  
  <p>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.</p>
  
  <p>So I would like to implement a pure SQL solution. I've been experimenting using the <code>posts_clauses</code> 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.</p>
  
  <pre><code>$clauses['fields'] .= ", $wpdb-&gt;posts.ID AS postID, (SELECT $wpdb-&gt;terms.term_id 
     FROM $wpdb-&gt;posts, $wpdb-&gt;term_taxonomy, $wpdb-&gt;term_relationships, $wpdb-&gt;terms     
     WHERE $wpdb-&gt;posts.ID=postID 
       AND $wpdb-&gt;term_taxonomy.taxonomy='post-series' 
       AND $wpdb-&gt;term_relationships.term_taxonomy_id=$wpdb-&gt;term_taxonomy.term_taxonomy_id 
       AND $wpdb-&gt;posts.ID=$wpdb-&gt;term_relationships.object_id 
       AND $wpdb-&gt;terms.term_id=$wpdb-&gt;term_taxonomy.term_id 
     ORDER BY $wpdb-&gt;posts.post_date DESC 
     LIMIT 0,1) AS uniqueID";
  
  $clauses['groupby'] = "IFNULL(uniqueID,$wpdb-&gt;posts.ID)";
  </code></pre>
  
  <p>Based on the clauses above WordPress constructs the following SQL query:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I've also read <a href="http://scribu.net/wordpress/sortable-taxonomy-columns.html">this tutorial</a>, but I'm not sure it applies in this case. </p>
  
  <p>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.</p>