Custom Post Type, WP_Query and ‘orderby’

by

I do have a custom post type with the following setup:

$supports = array(
    'title'
    , 'editor'
    , 'thumbnail'
    , 'revisions'
    , 'page-attributes'
);

$args = array(
  'hierarchical' => true
  , 'supports' => $supports
  [...]
);

register_post_type('myType', $args);

I would like to show all posts and have them sorted like in the wp-admin area (indention is for readability):

1, 
2, 
3, 
   1, (parent 3)
   2, (parent 3)
4

Therefore I tried the following query with the order-type set to ‘menu_order’:

$loop = new WP_Query( array(
       'post_type' => 'myType'
     , 'posts_per_page' => 50
     , 'orderby' => 'menu_order'
     , 'order' => 'ASC'
));

Unfortunately all post are sorted by menu_order, but by menu_order exclusively, ignoring the parent relationship (post_parent attribute). So I get something like this

1,
1, (parent 3)
2, 
2, (parent 3)
3,
4

Changing the query to 'orderby' => 'parent menu_order' leads to the following

1,
2, 
3,
4
1, (parent 3)
2, (parent 3)

So all in all it looks to me, as everything works as designed and the orderby-value is directly translated to the corresponding SQL ‘Order By’.

Question

What is the easiest way to get the desired order?

SQL

I assume this is the main sql-query wordpress creates:

SELECT SQL_CALC_FOUND_ROWS wp_2_posts.ID 
FROM wp_2_posts 
WHERE 1=1 AND wp_2_posts.post_type="inhalt" AND (wp_2_posts.post_status="publish" OR wp_2_posts.post_status="private") 
ORDER BY wp_2_posts.post_parent, wp_2_posts.menu_order ASC LIMIT 0, 50

which is then followed up with:

SELECT wp_2_posts.* 
FROM wp_2_posts 
WHERE ID IN (40,42,44,46,48,50,52,54,56,58,60,76,62,65,69,71,74)

SELECT post_id, meta_key, meta_value 
FROM wp_2_postmeta 
WHERE post_id IN (40,42,44,46,48,50,52,54,56,58,60,62,65,74,69,71,76)

Workaround

A known workaround, but not an answer is to give all posts higher and more “spaced” order values, like

100,
200, 
300,
   310,
   320,
400

2 Answers
2

As far as I can tell, there is no work-around to this at the database level. This is a problem I run into somewhat often, which is the case you need to turn an list with structure references into an an ordered array with children elements appearing immediately after their parents. This can be accomplished in PHP, but while this solution is fairly compact, it isn’t terribly straight-forward.

The following solution adds a filter to the the_posts filter, which structures and then flattens the result set with a recursive function.

// Add each level's child posts to the result list, in order
function recursively_flatten_list( $list, &$result ) {
    foreach( $list as $node ) {
        $result[] = $node['post'];
        if( isset( $node['children'] ) )
            recursively_flatten_list( $node['children'], $result );
    }
}

function my_sort_posts( $posts, $query ) {
    // Don't do outside admin. Only operate on main query. Only operate on queries for pages.
    if( is_admin() || !$query->is_main_query() || $query->get( 'post_type' ) != 'page' )
        return;

    $refs = $list = array();
    // Make heirarchical structure in one pass.
    // Thanks again, Nate Weiner:
    // http://blog.ideashower.com/post/15147134343/create-a-parent-child-array-structure-in-one-pass
    foreach( $posts as $post ) {
        $thisref = &$refs[$post->ID];

        $thisref['post'] = $post;

        if( $post->post_parent == 0)
            $list[$post->ID] = &$thisref;
        else
            $refs[$post->post_parent]['children'][$post->ID] = &$thisref;
    }

    // Create single, sorted list
    $result = array();
    recursively_flatten_list( $list, $result );

    return $result;
}
add_filter( 'the_posts', 'my_sort_posts', 10, 2 );

I’ve tested this solution and it’s general enough for arbitrary page hierarchies.

This code assumes that the posts are already ordered by menu_order. If you are to use this solution, be sure to change the orderby parameter to just "menu_order" where you make the call to new WP_Query.

Leave a Comment