0

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).

0

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.

1
  • Both variants produce identical order of posts (tested directly via MySQL in PHPMyAdmin, to exclude any filters)
    – Artem
    Sep 23 '21 at 8:21
0

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 );

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.