1

I'm building out a custom taxonomy archive page that needs to list terms in order by their associated post's post_date. I wrote this, it's working, but I hate resorting to custom SQL:

global $wpdb;

$post_types = array( 'series' );
$taxonomy   = array( 'taxonomy' );
$order      = 'DESC';

$sql = "SELECT DISTINCT t.*
    FROM $wpdb->terms AS t
    INNER JOIN $wpdb->term_taxonomy AS tt
        ON t.term_id = tt.term_id
    JOIN $wpdb->term_relationships AS tr
        ON tr.term_taxonomy_id = tt.term_taxonomy_id
    JOIN $wpdb->posts AS p
        ON p.ID = tr.object_id
    WHERE tt.taxonomy IN ( '" . implode( "','", $taxonomy ) . "' )
    AND p.post_type IN ( '" . implode( "','", $post_types ) . "' )
    ORDER BY p.post_date {$order};";

$terms_ordered_by_post_date = $wpdb->get_results( $sql );

Curious if anyone has a better solution?

4
  • "list terms in order by their associated post's post_date" - what if a term is applied to more than one post? Which post's post_date should matter? Jun 3, 2014 at 14:30
  • Awesome question! Setting DISTINCT means that the terms will be unique and ordered by the associated post_date. Edit original explanation was terrible, updated. Jun 3, 2014 at 14:56
  • I've submitted a core patch to see what feedback that might garner core.trac.wordpress.org/ticket/28439 Jun 3, 2014 at 15:01
  • I say ballpark, for anything at this level of logic you are usually doomed to custom SQL.
    – Rarst
    Jun 3, 2014 at 18:01

1 Answer 1

2

I would use the terms_clauses filter, and then just use get_terms():

function wpse147412_order_terms_by_post_date( $pieces, $taxonomies, $args ) {
    global $wpdb;

    if ( 'post_date' !== $args['orderby'] ) {
        return $pieces;
    }

    $args = wp_parse_args( $args, array( 'post_types' => 'post' ) );

    $pieces['fields']   = 'DISTINCT ' . $pieces['fields'];
    $pieces['join']    .= " JOIN $wpdb->term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id";
    $pieces['join']    .= " JOIN $wpdb->posts AS p ON p.ID = tr.object_id";
    $pieces['where']   .= " AND p.post_type IN ('" . implode( "', '", (array) $args['post_types'] ) . "')";
    $pieces['orderby']  = 'ORDER BY p.post_date';

    return $pieces;
}
add_filter( 'terms_clauses', 'wpse147412_order_terms_by_post_date', 10, 3 );

....

$terms = get_terms( 'taxonomy', array( 'post_types' => 'series', 'orderby' => 'post_date', 'order' => 'DESC' ) );
1
  • This is great, I'm going to switch my implementation to something that's filter based. Thank you! Jun 4, 2014 at 0:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.