Custom query based on meta key - Reduce three states to two in results?

Question

$args = array(  'post_type' => 'bars',
                'posts_per_page' => -1
                'orderby' => array( 'meta_value' => 'DESC', 'title' => 'ASC' ),
                'meta_query'  => array(
                     'relation' => 'OR',
                        array(
                          'key'     => 'featured_venue',
                          'compare' => 'NOT EXISTS',
                          ),

                        array(
                          'relation' => 'OR',
                                array(
                                  'key'   => 'featured_venue',
                                  'value' => true,
                                ),
                                array(
                                  'key'     => 'featured_venue',
                                  'value'   => 0,
                                ),
                        ),
                  ),

);

$query = new WP_Query($args);

I have above query in place for post type named bars. The bar CPT uses ACF to make a bar featured using ACF's True/False checkbox (https://www.advancedcustomfields.com/resources/true-false/) - ACF is creating the meta key featured_venue. However, since the ACF field was added to existing posts, the meta key is not present in all posts. So i have the following post states:

1. Post with featured_venue = true (1)
2. Post with featured_venue = false (0)
3. Post with meta key featured_venue not existing

My goal is it to list all venues and have featured venues first, and then all others in alphabetical order. However, my query currently give me the result as follows:

1. Featured Venues alphabetically
2. Posts with featured_venue metakey = false alphabetically
3. Posts without featured Venue metakey alphabetically.

I tried changing the compare values, but could not get the desired results. Instead it always dropped either the Posts with meta_key, or the ones without.

Is it possible to modify the query in my intended way, or do I need to bulk add the featured_venue meta key to all posts?

Answer 1

This can be done by providing a "default" value to the ORDER BY statement.

Try adding this before your call new WP_Query($args);:

$setDefaultOrderby = function($statement) {
    return str_replace('wp_postmeta.meta_value', "COALESCE(wp_postmeta.meta_value, 'false')", $statement);
};

add_filter('posts_orderby', $setDefaultOrderby);

and this after it:

remove_filter('posts_orderby', $setDefaultOrderby);

Change the 'false' with whatever the default value is that you need.

I am using COALESCE here, which basically selects a first argument you pass to it that is not NULL, essentially allowing you to provide a fallback for a post that has no meta key.