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.
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.
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.
Note that post.id = 629 appears twice in the results:
The duplicate rows are filtered out by adding a
NOT EXISTS(...)
condition:And the results, posts sorted by child within parent, and no duplicate records:
UPDATE: Function NOT current with the latest SQL above
And yes, no need to use
prepare()
for query parts that only have the default table names.