What's wrong with this meta query? (order by meta key, then title, doesn't work)

Question

Here's my meta query:

$taxonomy_term = 1494;
$args['meta_query'] = array(
        'relation' => 'OR',
        'mp_exists' => array(
            'key' => 'tdlrm_mp_'.$taxonomy_term,
            'type' => 'NUMERIC',
        ),
        'mp_not_exists' => array(
            'key' => 'tdlrm_mp_'.$taxonomy_term,
            'compare' => 'NOT EXISTS'
        )
    );
$args['orderby'] = array( 'meta_value_num' => 'ASC', 'title' => 'ASC' );

The posts where 'tdlrm_mp_'.$taxonomy key exists get placed first as expected. Hovewer, the rest do not get ordered by their title (actually, I can't figure out what they are ordered by). What am I doing wrong?

This answer doesn't help. I wrote my query according to this manual.

Update

I ran WP's MySQL query in my database and saw the postmeta table joined twice. The posts that have no tdlrm_mp_{term} meta key still have some other meta key in the resulting table and the rest of the posts get ordered by the value of that meta. (mp_exists adds the first postmeta table, mp_not_exists adds the second one, where mp_exists does not exist. Still some other meta key exists in the first joined table, so the posts keep getting ordered by that column).

Answer 1

Turns out, I had to use a 'posts_orderby' filter. I turned to Sally CJ's answers to my question and another question. I thought I wouldn't need it, but I was wrong.

The reason posts do not get ordered properly is that in the MySQL query WP produces, the postmeta table get joined several times, resulting in multiple meta_key columns. So, in the first of the meta_key columns, where the meta key isn't 'tdlrm_mp_'.$taxonomy_term, there still is some other meta key, so the posts just keep being ordered by the meta values corresponding to this column, like so:

/*
| wp_postmeta.meta_key | wp_postmeta.meta_value | mt1.meta_key  | mt1.meta_value |
----------------------------------------------------------------------------------
| tdlrm_mp_1494        | 1                      | tdlrm_mp_1494 | 1              |
----------------------------------------------------------------------------------
| tdlrm_mp_1494        | 2                      | tdlrm_mp_1494 | 2              |
----------------------------------------------------------------------------------
| some_other_key       | X                      | NULL          | NULL           |
----------------------------------------------------------------------------------
| some_other_key       | Y                      | NULL          | NULL           |
----------------------------------------------------------------------------------
*/

To deal with this, we need do add case statements to our ORDER BY, so that the meta_value column would be used only to order posts that actually have tdlrm_mp_1494 meta key in it.

We can achieve this by setting up an orderby filter.

This is how:

Here are our meta query arguments. Note the orderby argument set to false (we do not need it).

$taxonomy = 1494; // for demonstration purposes
$args['meta_query'] = array(
        'relation' => 'OR',
        'mp_exists' => array(
            'key' => 'tdlrm_mp_'.$taxonomy,
            'type' => 'NUMERIC',
        ),
        'mp_not_exists' => array(
            'key' => 'tdlrm_mp_'.$taxonomy,
            'compare' => 'NOT EXISTS'
        )
    );
$args['orderby'] = false;

First, we add a custom variable to our query arguments, to let WP know where to use the filter:

$args['tdlrm_commands'] = array(
        'filter' => 'orderby_filter_1',
        'key' => 'tdlrm_mp_'.$taxonomy
    );

Second, we add the filter (to functions.php or a plugin file, whatever):

add_filter( 'posts_orderby', function ( $orderby, $query ) {

    //won't fire for other queries
    if(
    empty($query->query['tdlrm_commands']['do'])
    || $query->query['tdlrm_commands']['do'] !== 'orderby_filter_1'
    )
    {
    return $orderby;
    }

    global $wpdb;

    $meta_clauses = $query->meta_query->get_clauses();
    
    //get the key we passed in our custom argument
    $key = $query->query['tdlrm_commands']['key'];

    //get the table alias (remember there are multiple postmeta tables joined together,
    //we need only the one that satisfies our meta query clause, `mp_exists`)
    $has_tdlrm_mp = $meta_clauses['mp_exists']['alias'];


    /*
    in the postmeta table with alias {$has_tdlrm_mp},
    when meta_key equals to {$key},
    use the first clause,
    otherwise, use the second.

    first clause:
    in the postmeta table with alias {$has_tdlrm_mp},
    when meta_key equals to {$key},
    order by its meta value, in the ascending order
    (+0 casts a string to number, otherwise 21 will go after 2 and before 3.)

    second clause:
    if it's not, order by title, in the ascending order
    */
    $orderby = " CASE {$has_tdlrm_mp}.meta_key
                    WHEN '{$key}' THEN 1
                    ELSE 2
                END ASC,
                CASE {$has_tdlrm_mp}.meta_key
                    WHEN '{$key}' THEN {$has_tdlrm_mp}.meta_value+0
                END ASC,
                {$wpdb->posts}.post_title ASC";

    return $orderby;
}, 10, 2 );

Answer 2

I think the problem is that you have multiple meta values so your order by should be more targeted, like this.

$args['orderby'] = array(
    'mp_exists'     => 'ASC',
    'mp_not_exists' => 'ASC',
    'title'         => 'ASC'
);

I don't know by what meta you want to order so I added both of them, remove as necessary.