Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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.

share|improve this question
    
First thing - work out the SQL query you'd need to do this. I see a recursive self-join of wp_term_taxonomy in your future. Thinking out loud, the query can only return a list of posts -- and you only want each post to appear once at it's leaf taxon level. Something like this: SO: recursive self join SQL –  marfarma Oct 15 '12 at 16:57
    
Once you have all the leaf nodes, you'd add an outer join to pick up the parent taxons. Calculate column order_term = is_null(parent, child) and order by order_term –  marfarma Oct 15 '12 at 17:01
    
@marfarma Would you be able to add an answer? :) –  kaiser Oct 15 '12 at 17:12
    
If I had some real data to play with, I could work out the SQL -- lets continue to discuss in chat –  marfarma Oct 15 '12 at 17:17

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
Ok, I now upvoted on a half working solution :) I hope you don't mind the edit and the notes. Just to show the progress. –  kaiser Oct 15 '12 at 20:05
1  
Hqppy to have the edits. Just fixed the orderby in my and your versions of the code. –  marfarma Oct 15 '12 at 20:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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