WP_Query cannot properly order by nested Parent/Child relationships

[son9ne]

Steps to reproduce the problem:

• Create a new CPT named series.
• Create numerous parent/child relationships in no particular order. Some are nested since we have the concept of seasons and episoded in series. These will not fit properly with taxonomies because data for each "Season 1" is unique to the parent series.
• The parent can be added after the child is created and then associated (important as the date is taken into account here as it is unreliable in this case and is a very realistic scenario).
• use menu_order as a way to order these items. Seasons menu_order will be the season number and episodes are most commonly going to be used 1-10. One could argue season 2 should be 20-30 but that is not normal user expectation when using the system (I already have teams working on this and they all favor 1-10 and not 20... 30... per season. I tried...)

Basically, what you are creating is a bunch of series that have seasons as their immediate children then these seasons have episodes assigned to them. Typical series structure.

Series (parent) -> Season (child of Series, parent of Episode) -> Episode (child of Season).

I can easily filter the results for parents and list the children. The issue I have is that I am not able to sort by children properly.

What I expect is a structure like:

Game of Thrones

- Season 1
-- Episode 1
-- Episode 2
-- Episode 3
- Season 2
-- Episode 1
-- Episode 2
-- Episode 3
- Season 3
-- Episode 1
-- Episode 2
-- Episode 3
...

What I get:
Game of Thrones

- Season 1
- Season 2
- Season 3
-- Episode 1 - season 3
-- Episode 2 - season 3
-- Episode 3 - season 3
-- Episode 1 - season 1
-- Episode 2 - season 1
-- Episode 3 - season 1
-- Episode 1 - season 2
-- Episode 2 - season 2
-- Episode 3 - season 2
...

I have tried playing with the orderby for all available options and I can not get the structure I expect.

It seems like this is a limitation with the table design as menu_order and post_parent are not enough to create this structure. In fact, you can pretty much only do what I am getting, not what I would expect. It would seem that we need a better way to handle deeper parent/child relationships for this to order properly. Perhaps a meta field would help with this.

I'm not sure if there is anything that can be done here. I am very doubtful. I may have to rethink the system and add more meta fields for ordering as this is going to be a problem for the project.

Reporting this here to see if it's worth anyone's time to research.

I've included my class to help with testing.

<?php

namespace Parables\WP\Core\Backend;

use Parables\WP\Core\VideoFactory;

/**
 * Class SeriesAdminListFilter
 * This will add a filter option for series parents to the admin list
 * This allows viewing of children only for seasons
 * @package Parables\WP\Core\Backend
 */
class SeriesAdminListFilter {

    /**
     * Stores all series parents
     * @var array
     */
    private $allSeriesParents;

    /**
     * Request parameter
     */
    const REQUEST_SERIES_NAME_VARIABLE = 'series_id';

    /**
     * Assign hooks
     */
    public function __construct() {
        \add_action('restrict_manage_posts', array($this, 'addDropDownFilter'), 10, 2);
        \add_action('parse_query', array($this, 'filterQuery'), 10);
    }

    /**
     * Build the drop down menu
     * @param string $post_type
     * @param string $which
     */
    public function addDropDownFilter($post_type, $which) {
        if ('series' !== $post_type) {
            return; //check to make sure this is your cpt
        }
        $_series = $this->_getAllSeriesParents();
        if (empty($_series)) {
            return;
        }

        $selected = isset($_REQUEST[self::REQUEST_SERIES_NAME_VARIABLE]) ? $_REQUEST[self::REQUEST_SERIES_NAME_VARIABLE] : '';
        ?>
        <select id="<?php esc_attr_e(self::REQUEST_SERIES_NAME_VARIABLE); ?>"
                name="<?php esc_attr_e(self::REQUEST_SERIES_NAME_VARIABLE); ?>">
            <option value=""><?php _e('All Series', 'parables_core'); ?></option>
            <?php foreach ($_series as $sID => $sTitle) : ?>
                <option value="<?php esc_attr_e($sID); ?>" <?php echo ($selected == $sID) ? 'selected="selected"' : ''; ?>><?php echo $sTitle; ?></option>
            <?php endforeach; ?>
        </select>
        <?php
    }

    /**
     * Filter the Query
     * @param \WP_Query $query
     * @return \WP_Query
     */
    public function filterQuery($query) {
        // Check if backend main query
        if (!is_admin() || !$query->is_main_query()) {
            return $query;
        }
        // Ensure proper post_type and that we have the request var
        if ('series' !== $query->query['post_type'] || empty($_REQUEST[self::REQUEST_SERIES_NAME_VARIABLE])) {
            // No need to filter, not where we want to be
            return $query;
        }
        // Ok, let's extend the WP_Query to use our filter
        // Fetch Series Object so we can extract the season post IDs
        $_seriesObject  = VideoFactory::getObject($_REQUEST[self::REQUEST_SERIES_NAME_VARIABLE]);
        $_seriesSeasons = $_seriesObject->getSeasons();
        $_seriesSeasons = array_keys($_seriesSeasons); // gets the season post ID
        // build $_parentIDs to be used to limit the result set
        $_parentIDs = [(int)$_seriesObject->getPostID()];
        $_parentIDs = array_merge($_parentIDs, $_seriesSeasons);
        // Modify the query object to use our new filter
        $query->query_vars['post_parent__in'] = $_parentIDs;
        $query->query_vars['orderby']         = [
            'parent'     => 'ASC',
            'menu_order' => 'ASC'
        ];

        return $query;
    }

    /**
     * Returns all series parents
     * @return mixed
     */
    private function _getAllSeriesParents() {
        if (NULL === $this->allSeriesParents) {
            // Build query for genre query
            $args = array(
                'posts_per_page'      => -1,
                'post_status'         => 'publish',
                'orderby'             => 'title',
                'order'               => 'ASC',
                'post_parent'         => 0,
                'post_type'           => array(
                    'series'
                ),
                'ignore_sticky_posts' => 1
            );
            // Do query
            $wpQuery = new \WP_Query($args);
            if ($wpQuery->have_posts()) {
                foreach ($wpQuery->get_posts() as $p) {
                    // Normal loop logic using $p as a normal WP_Post object
                    $this->allSeriesParents[$p->ID] = $p->post_title;
                }
            }
        }

        return $this->allSeriesParents;
    }

}

Series CPT is:

<?php
        $capabilityType = 'series';
        register_post_type('series',
            array(
                'labels'          => array(
                    'name'                  => __('Series', 'parables_core'),
                    'singular_name'         => __('Series', 'parables_core'),
                    'featured_image'        => __('Poster Image', 'parables_core'),
                    'set_featured_image'    => __('Set Poster Image', 'parables_core'),
                    'remove_featured_image' => __('Remove Poster Image', 'parables_core'),
                    'use_featured_image'    => __('Use Poster Image', 'parables_core'),
                ),
                'public'          => true,
                'menu_position'   => 21,
                'menu_icon'       => 'dashicons-video-alt2',
                'hierarchical'    => true,
                'capability_type' => 'series',
                'capabilities'    => array(
                    'edit_post'              => "edit_{$capabilityType}",
                    'read_post'              => "read_{$capabilityType}",
                    'delete_post'            => "delete_{$capabilityType}",
                    'edit_posts'             => "edit_{$capabilityType}s",
                    'edit_others_posts'      => "edit_others_{$capabilityType}s",
                    'publish_posts'          => "publish_{$capabilityType}s",
                    'read_private_posts'     => "read_private_{$capabilityType}s",
                    'delete_posts'           => "delete_{$capabilityType}s",
                    'delete_private_posts'   => "delete_private_{$capabilityType}s",
                    'delete_published_posts' => "delete_published_{$capabilityType}s",
                    'delete_others_posts'    => "delete_others_{$capabilityType}s",
                    'edit_private_posts'     => "edit_private_{$capabilityType}s",
                    'edit_published_posts'   => "edit_published_{$capabilityType}s"
                ),
                'rewrite'         => array(
                    'with_front' => false
                ),
                'supports'        => array(
                    'title',
                    'editor',
                    'page-attributes',
                ),
            )
        );