Order posts by (hierarchical custom) taxonomy terms and term children

Question

The scenario

• a custom post type wiki
• a (hierarchical) custom taxonomy topics
• a page template archive-wiki.php

The situation

Posts show up and get ordered by post_date (which is the default).

The according core query is:

SELECT SQL_CALC_FOUND_ROWS {$wpdb->prefix}posts.ID 
    FROM {$wpdb->prefix}posts WHERE 1=1 
    AND {$wpdb->prefix}posts.post_type = 'wiki' 
    AND ({$wpdb->prefix}posts.post_status = 'publish' OR {$wpdb->prefix}posts.post_status = 'private') 
ORDER BY {$wpdb->prefix}posts.post_date 
DESC 
LIMIT 0, {$setting->posts_per_page}

Yeah, I'm logged in when grabbing this query - hence status += private.

The task

Order all posts by the custom hierarchy main terms. Inside the main taxon, assign the posts to the subterms. So the main array only contains the taxonomies main taxons with the actual wiki-posts assigned to their corresponding subtaxons. If they're not assigned to a sub taxon or the taxon has no child taxons, then it should simply be added to the main array.

Where should this happen?

Inside the pre_get_posts or posts_pieces (and similar filters). I don't want to add an additional query for this.

Thank you.

Answer 1

This query will handle two levels of hierarchy in your taxonomy. More than two levels of hierarchy and you'll need a recursive self-join.

What this does is return the posts in the correct child within parent order. To create the appropriate parent level headings, you'll have compare the current post's parent taxon with that of the prior post. Print the taxon heading at the top of the page, and another level at each point where the parent taxon value changes. You'll need to call a function on each post to get the parent level taxon, as I don't know of any way to pass the value from the query into the post object.

 SELECT *
   from wp_posts 
  LEFT JOIN wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_id
    LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
    LEFT JOIN wp_terms ON (wp_term_taxonomy.term_id = wp_terms.term_id)
    LEFT JOIN wp_term_taxonomy AS parent ON (wp_term_taxonomy.parent = parent.term_taxonomy_id)
    LEFT JOIN wp_terms AS parent_terms ON (parent.term_id = parent_terms.term_id) 
  WHERE wp_posts.post_type = 'post'
  AND wp_term_taxonomy.taxonomy = 'category'
  and not exists(select 1 
                   from wp_posts AS subposts
                        LEFT JOIN wp_term_relationships as subtr ON subposts.ID = subtr.object_id
                        LEFT JOIN wp_term_taxonomy as subtt ON (subtr.term_taxonomy_id = subtt.term_taxonomy_id)
                        LEFT JOIN wp_terms as subt ON (subtt.term_id = subt.term_id)
                        LEFT JOIN wp_term_taxonomy AS subparent ON (subtt.parent = subparent.term_taxonomy_id)
                  where subtt.parent > wp_term_taxonomy.parent
                    and subposts.ID = wp_posts.ID 
                    AND subtt.taxonomy = wp_term_taxonomy.taxonomy)
  ORDER BY IFNULL(  parent_terms.slug ,  wp_terms.slug) ASC, wp_terms.slug  ASC;

Given that this is the correct SQL for your archive page, let's consider how to implement using filters, such as pre_get_posts, etc.

UPDATE The SQL query above has been tested and modified to return correct results. The following, similar to my original query, returns two rows for any post that has a parent.

   SELECT wp_posts.ID,  parent_terms.slug parent_slug, wp_terms.slug, wp_term_taxonomy.taxonomy, wp_term_taxonomy.parent
         , IFNULL(  parent_terms.slug ,  wp_terms.slug) sort_col2
   from wp_posts 
   LEFT JOIN wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_id
    LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
    LEFT JOIN wp_terms ON (wp_term_taxonomy.term_id = wp_terms.term_id)
    LEFT JOIN wp_term_taxonomy AS parent ON (wp_term_taxonomy.parent = parent.term_taxonomy_id)
    LEFT JOIN wp_terms AS parent_terms ON (parent.term_id = parent_terms.term_id) 
  WHERE wp_posts.post_type = 'post'
  AND wp_term_taxonomy.taxonomy = 'category'
  ORDER BY IFNULL(  parent_terms.slug ,  wp_terms.slug) ASC, wp_terms.slug  ASC;

Note that post.id = 629 appears twice in the results:

  +-----+-------------+---------------+----------+--------+-----------+
  | ID  | parent_slug | slug          | taxonomy | parent | sort_col2 |
  +-----+-------------+---------------+----------+--------+-----------+
  | 629 |             | business      | category | 0      | business  |
  | 629 | business    | press-release | category | 3      | business  |
  | 618 |             | media         | category | 0      | media     |
  | 608 |             | media         | category | 0      | media     |
  | 624 |             | startups      | category | 0      | startups  |
  | 621 |             | startups      | category | 0      | startups  |
  +-----+-------------+---------------+----------+--------+-----------+
  6 rows in set (0.00 sec)

The duplicate rows are filtered out by adding a NOT EXISTS(...) condition:

  SELECT wp_posts.ID,  parent_terms.slug parent_slug, wp_terms.slug, wp_term_taxonomy.taxonomy, wp_term_taxonomy.parent
         , IFNULL(  parent_terms.slug ,  wp_terms.slug) sort_col2
   from wp_posts 
  LEFT JOIN wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_id
    LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
    LEFT JOIN wp_terms ON (wp_term_taxonomy.term_id = wp_terms.term_id)
    LEFT JOIN wp_term_taxonomy AS parent ON (wp_term_taxonomy.parent = parent.term_taxonomy_id)
    LEFT JOIN wp_terms AS parent_terms ON (parent.term_id = parent_terms.term_id) 
  WHERE wp_posts.post_type = 'post'
  AND wp_term_taxonomy.taxonomy = 'category'
  and not exists(select 1 
                   from wp_posts AS subposts
                        LEFT JOIN wp_term_relationships as subtr ON subposts.ID = subtr.object_id
                        LEFT JOIN wp_term_taxonomy as subtt ON (subtr.term_taxonomy_id = subtt.term_taxonomy_id)
                        LEFT JOIN wp_terms as subt ON (subtt.term_id = subt.term_id)
                        LEFT JOIN wp_term_taxonomy AS subparent ON (subtt.parent = subparent.term_taxonomy_id)
                  where subtt.parent > wp_term_taxonomy.parent
                    and subposts.ID = wp_posts.ID )
  ORDER BY IFNULL(  parent_terms.slug ,  wp_terms.slug) ASC, wp_terms.slug  ASC;

And the results, posts sorted by child within parent, and no duplicate records:

  +-----+-------------+---------------+----------+--------+-----------+
  | ID  | parent_slug | slug          | taxonomy | parent | sort_col2 |
  +-----+-------------+---------------+----------+--------+-----------+
  | 629 | business    | press-release | category | 3      | business  |
  | 618 |             | media         | category | 0      | media     |
  | 608 |             | media         | category | 0      | media     |
  | 624 |             | startups      | category | 0      | startups  |
  | 621 |             | startups      | category | 0      | startups  |
  +-----+-------------+---------------+----------+--------+-----------+
  5 rows in set (0.00 sec)

---

UPDATE: Function NOT current with the latest SQL above

function wpse69290_query( $pieces, $obj )
{
    global $wpdb;

    #$pieces['fields'] = "* ";

    $pieces['join'] .= " LEFT JOIN `$wpdb->term_relationships` AS trs ON ($wpdb->posts.ID = trs.object_id)";
    $pieces['join'] .= " LEFT JOIN `$wpdb->term_taxonomy` AS tt ON (trs.term_taxonomy_id = tt.term_taxonomy_id)";
    $pieces['join'] .= " LEFT JOIN `$wpdb->terms` AS t ON (tt.term_id = t.term_id)";
    $pieces['join'] .= " LEFT JOIN `$wpdb->term_taxonomy` AS parent ON (parent.parent = trs.term_taxonomy_id)";
    $pieces['join'] .= " LEFT JOIN `$wpdb->terms` AS parent_terms ON (parent.term_id = parent_terms.term_id)";

    $pieces['where'] .= " AND (tt.taxonomy = 'topics')";

    $pieces['orderby'] = "IFNULL(parent_terms.slug, t.slug) ASC";

    $pieces['limits'] = "LIMIT 0, 999";

    return $pieces;
}
add_filter( 'posts_clauses', 'wpse69290_query', 10, 2 );

And yes, no need to use prepare() for query parts that only have the default table names.