wp_query orderby with multiple metas (same key) not working as expected

Question

I'm trying to order a custom post type with a custom field (Boolean) that flags if a post is featured or not. If it's featured it shows on top of the list. This custom field returns 3 values: '1', '0' or '', so I created 3 metas for each compare operator.

The problem is that the 'regular_posts' appears always first and 'featured_posts' next, like they are one solid group. The 'empty_value' shows up last. The optimum solution would be to first show 'featured_posts' followed by 'regular_posts' and 'empty_value' as one group.

There are also some conditional metas and tax that will work on top of that for filtering purposes. The problem occurs before this filters come into play. Nevertheless I'll include the code.

function get_projetos_posts()
{
    global $wp_query;

    if ($wp_query->is_main_query() && !is_admin()) {

        if (is_post_type_archive('iniciativas')) {

            // -> filter vars 
            global $filter_areas, $filter_inicia;

            $paged = get_query_var('paged') ? get_query_var('paged') : 1;

            // -> main args
            $args = array(
                'post_type' => 'iniciativas',
                'meta_query' => array(
                    'relation' => 'AND',
                    array(
                        'relation' => 'OR',
                        'featured_posts' => array(
                            'key' => 'inicia_featured',
                            'value' => '1',
                            'compare' => '='
                        ),
                        'regular_posts' => array(
                            'key' => 'inicia_featured',
                            'value' => '0',
                            'compare' => '='
                        ),
                        'empty_value' => array(
                            'key' => 'inicia_featured',
                            'compare' => 'NOT EXISTS',
                        )
                    )
                ),
                'orderby'    => array(
                    'featured_posts' => 'ASC',
                    'regular_posts'  => 'ASC',
                    'empty_value'    => 'ASC'
                ),
                'order'      => 'DESC',
                'paged' => $paged,
            );

            // -> Filter by cats
            if ($filter_inicia && count($filter_inicia) > 0) {
                $cat_inicia = [];
                foreach ($filter_inicia as $filter) {
                    // get area post object from slug/post_name
                    array_push($cat_inicia, get_term_by('slug', $filter, 'iniciativa_cat')->term_id);
                }
                $args['tax_query']['relation'] = 'OR';
                $args['tax_query'] = array(
                    array(
                        'taxonomy' => 'iniciativa_cat',
                        'field' => 'id',
                        'terms' => $cat_inicia
                    )
                );
            }

            // -> Filter by ACF fields
            if ($filter_areas && count($filter_areas) > 0) {
                $meta_areas = array();
                $meta_areas['relation'] = 'OR';
                foreach ($filter_areas as $filter) {
                    // get area post object from slug/post_name
                    $value = get_page_by_path($filter, OBJECT, 'areas');
                    // set query with area post ID
                    $meta_areas[] = array(
                        'key' => 'inicia_areas',
                        'value' => $value->ID,
                        'compare' => 'LIKE'
                    );
                }
                $args['meta_query'][] = $meta_areas;
            }
        }
    }

    $the_query = new WP_Query($args);

    return $the_query;
}

I tried to change the orderby and order values to check how it affects the query and the 'regular_posts' always shows up first or last on the list (depending of the order value), followed be 'featured_posts' and finally the 'empty_value'. The result should be all 'featured_posts' first followed by 'regular_posts' and 'empty_value' as one single list of posts, subject to one orderby value (if that's even possible with this code).

UPDATE 1

After some trial and error, I figured that the order of items for orderby array are relevant and this was the order that worked best:

 'orderby' => array(
              'empty_value' => 'DESC',
              'date' => 'DESC',
              'featured_posts' => 'DESC'
          ),

With this change the 'featured_posts' are shown first, then 'regular_posts' and lastly the 'empty_value'. Not the optimal solution, because 'regular_posts'and 'empty_value' should be ordered as one seamless list, but to achieve that, those arrays should be nested into one parent array and that impacted on the page generation time (from .34s to .44s).

I'll keep this open until I find a better solution.

Cheers!

Answer 1

So, I've ended up discarding the original code for a more verbose/reliable one, using multiple queries - for featured posts and remaining posts - and merging them together for one final query. This post type won't grow much in terms of number of posts, so, efficiency is not a big factor here.

function get_projetos_posts()
{
    global $wp_query;

    if ($wp_query->is_main_query() && !is_admin()) {

        if (is_post_type_archive('iniciativas')) {

            // -> Filter posts by meta and tax **************
            function filter_args($args)
            {
                global $filter_inicia, $filter_areas;

                // -> Filtrar por categorias
                if ($filter_inicia && count($filter_inicia) > 0) {
                    $cat_inicia = [];
                    foreach ($filter_inicia as $filter) {
                        // get area post object from slug/post_name
                        array_push($cat_inicia, get_term_by('slug', $filter, 'iniciativa_cat')->term_id);
                    }
                    $args['tax_query']['relation'] = 'OR';
                    $args['tax_query'] = array(
                        array(
                            'taxonomy' => 'iniciativa_cat',
                            'field' => 'id',
                            'terms' => $cat_inicia
                        )
                    );
                }

                // -> Filtrar por ACF fields
                if ($filter_areas && count($filter_areas) > 0) {
                    $meta_areas = array();
                    $meta_areas['relation'] = 'OR';
                    foreach ($filter_areas as $filter) {
                        // get area post object from slug/post_name
                        $value = get_page_by_path($filter, OBJECT, 'areas');
                        // set query with area post ID
                        $meta_areas[] = array(
                            'key' => 'inicia_areas',
                            'value' => $value->ID,
                            'compare' => 'LIKE'
                        );
                    }
                    $args['meta_query'][] = $meta_areas;
                }

                return $args;
            }

            // Set vars and constants
            $paged = get_query_var('paged') ? get_query_var('paged') : 1;
            $featured_ids = '';
            $featured_query = [];

            // -> Featured Posts ****************************
            $featured_args = array(
                'post_type' => 'iniciativas',
                'posts_per_page' => -1,
                'meta_query' => array(
                    'relation' => 'AND',
                    'featured_posts' => array(
                        'key' => 'inicia_featured',
                        'value' => '1',
                        'compare' => '='
                    )
                ),
                'orderby'    => 'date',
                'order'      => 'DESC',
            );
            // Call Function to Filter Posts and make last changes to args
            $final_feat_args = filter_args($featured_args);
            // Create query
            $featured_query = new WP_Query($final_feat_args);
            wp_reset_postdata();
            // Get all post ids
            $featured_ids = wp_list_pluck($featured_query->posts, 'ID');

            //  -> Remaining Posts **************************
            $remain_args = array(
                'post_type' => 'iniciativas',
                'posts_per_page' => -1,
                'orderby'    => 'date',
                'order'      => 'DESC',
                'post__not_in' => $featured_ids,
            );
            // Call Function to Filter Posts and make last changes to args
            $final_remain_args = filter_args($remain_args);
            // Create query
            $remaining_posts_query = new WP_Query($final_remain_args);
            wp_reset_postdata();
            // Get all post ids
            $remain_ids = wp_list_pluck($remaining_posts_query->posts, 'ID');

            // -> Final Query *******************************
            $final_ids =  array_merge($featured_ids, $remain_ids);

            $final_args = array(
                'post_type' => 'iniciativas',
                'posts_per_page' => 12,
                'post__in' => $final_ids,
                'orderby' => 'post__in',
                'paged' => $paged
            );
            $final_query = new WP_Query($final_args);
        }
        return $final_query;
    }
}