WP_Query produces wrong SQL

The structure of your meta_query array is incorrect. The inner arrays should be indexed, your var_dump indicates you’re using associative relations. A proper structure would show the inner arrays indexed with 0, 1, etc., not list_prio, list_no_prio, etc.

Review examples of meta_query usage on the WP_Query docs page.

Thanks for the reply! The docs specifically say:

If you wish to order by two different pieces of postmeta (for example, City first and State second), you need to combine and link your meta query to your orderby array using ‘named meta queries’.

And they provide an example. That’s why I used associative. I will try without and see if there’s a different sql output. Since I’m only sorting on one of them that might be the issue.

Hi again @bcworkz I have looked at my query again and I honestly don’t know how to accomplish an order on two different meta without using an associative array like in the example in the docs you linked to.

These are my args for the meta and ordering:

$loopArgs['meta_key'] = '_item_rating_value_key';
$loopArgs['meta_query'] = array(
    'relation' => 'OR',
    'list_prio' => array(
        'key' => '_affiliate_list_73_prio_value_key',
        'type' => 'NUMERIC',
        'compare' => 'EXISTS',
    ),
    'list_no_prio' => array(
        'key' => '_affiliate_list_73_prio_value_key',
        'type' => 'NUMERIC',
        'compare' => 'NOT EXISTS',
    ),
);
$loopArgs['orderby'] = array(
    'list_prio' => 'DESC',
    'meta_value_num' => 'DESC',
);

Since all posts have the key “_item_rating_value_key” I use [meta_key] to query those. Then I use [meta_query] to query the other key since I need all posts with or without that key using compare “EXISTS” and “NOT EXISTS”.
The funny thing it produces a correct SQL if I change the [orderby] arg to order on “list_no_prio” instead of “list_prio”. I have verified this by using the query in the db as well.

If I change my query to only use [meta_query] like in the docs example:

$loopArgs['meta_query'] = array(
'relation' => 'OR',
'list_prio' => array(
    'key' => '_item_rating_value_key',
    'type' => 'NUMERIC',
    'compare' => 'EXISTS',
),
'list_rating' => array(
    'key' => '_affiliate_list_73_prio_value_key',
    'type' => 'NUMERIC',
),
);
$loopArgs['orderby'] = array(
'list_prio' => 'DESC',
'list_rating' => 'DESC',
);

The SQL output is just wrong, see it doesn’t include the meta keys in the JOINS at all so a random meta key from db is queried and sorted on, the correct posts are queried but the order is on a random meta key not even in the query:

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts
LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) WHERE 1=1 
AND ( 
  wp_term_relationships.term_taxonomy_id IN (73)
) AND ( 
  wp_postmeta.meta_key = '_item_rating_value_key' 
  OR 
  mt1.meta_key = '_affiliate_list_73_prio_value_key'
) AND wp_posts.post_type IN ('post', 'page') AND ((wp_posts.post_status = 'publish' OR wp_posts.post_status = 'draft')) GROUP BY wp_posts.ID ORDER BY CAST(wp_postmeta.meta_value AS SIGNED) DESC, CAST(mt1.meta_value AS SIGNED) DESC LIMIT 0, 10

Do you have a suggestion how to solve this other than my fix right now to sort on the key that does not exist in order to trick wp_query into outputting the correct SQL ? I’m not sure how to do this query without using associative array to be honest so any help would be appreciated.

Even though I got a working query now it is obviously something wrong and I don’t want to revise if this is an actual bug that will be fixed in the future.

I stand corrected about ‘named meta queries’. It may have been an added feature that I’ve missed entirely. Thanks for pointing it out. I’m unclear what the SQL should look like when two different ordering criteria values occur in the same column. SQL isn’t my strong suit. If you do know the correct SQL, as a workaround you could patch up the SQL used through the “posts_request’ filter.

Ordering by a single meta key value uses an entirely different format, by providing a ‘meta_key’ arg and letting ‘meta_query’ arrays be the indexed kind. Like the example above the ‘named meta queries’ example (‘orderby’ with ‘meta_value’ and custom post type). If your need is to only sort by one key, this is the way you should do it.

Thanks for your reply @bcworkz !

Summarised my need is:

• Get all posts by a key they all have rating_key
• Firstly, order by another key that only some of them have prioritised_key
• Secondly, order by the key that they all have rating_key.

If I only query rating_key and prioritised_key then all of the posts not having the key prioritised_key will obviously be omitted, that’s why I need to also query the ones that don’t have prioritised_key. And this is where WP_Query seems to mix up the keys in the query..