Title: WP_Query produces wrong SQL
Last modified: April 6, 2022

---

# WP_Query produces wrong SQL

 *  [simonlindahl](https://wordpress.org/support/users/simonlindahl/)
 * (@simonlindahl)
 * [4 years, 2 months ago](https://wordpress.org/support/topic/wp_query-produces-wrong-sql/)
 * The issue is that WP_Query seems to mix up some things, let me show you.
 * It’s the meta_query that gets messed up in the SQL.
 * This is the WP_Query arguments and as you can see I order on “list_prio” and “
   meta_value_num”:
 *     ```
       Array
       (
           [post_type] => Array
               (
                   [0] => post
                   [1] => page
               )
   
           [post_status] => Array
               (
                   [0] => publish
                   [1] => draft
               )
   
           [posts_per_page] => 
           [cat] => 
           [meta_key] => _item_rating_value_key
           [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
                           [compare] => NOT EXISTS
                       )
   
               )
   
           [orderby] => Array
               (
                   [list_prio] => DESC
                   [meta_value_num] => DESC
               )
   
           [tax_query] => Array
               (
                   [0] => Array
                       (
                           [taxonomy] => list
                           [field] => term_id
                           [terms] => 73
                       )
   
               )
   
       )
       ```
   
 * The SQL produced by this looks like below and there are 2 errors here.
 * 1. The “ORDER BY” uses mt1 as order since that is the one in the meta_query BUT
   if you look in te LEFT JOIN for mt1 it is missing the `AND mt1.meta_key = '_affiliate_list_73_prio_value_key'`
   which makes the SQL query just get a random meta_key from the db instead and 
   order on that.
 * 2. In the LEFT JOIN with mt2 I do have the correct meta_key, but why in mt2, 
   that should be the group with no meta_key named like that. So what if I [orderby]
   on “list_no_prio” instead? Well scroll past this code block.
 *     ```
       SELECT 
         SQL_CALC_FOUND_ROWS wp_posts.ID,
         wp_posts.post_title, 
         mt2.meta_key, 
         CAST(mt2.meta_value AS SIGNED), 
         wp_casinohawks_postmeta.meta_key, 
         CAST(wp_casinohawks_postmeta.meta_value AS SIGNED) 
       FROM 
         wp_posts 
         LEFT JOIN wp_casinohawks_term_relationships ON (
           wp_posts.ID = wp_casinohawks_term_relationships.object_id
         ) 
         LEFT JOIN wp_casinohawks_postmeta ON (
           wp_posts.ID = wp_casinohawks_postmeta.post_id
         ) 
         LEFT JOIN wp_casinohawks_postmeta AS mt1 ON (
           wp_posts.ID = mt1.post_id
         ) 
         LEFT JOIN wp_casinohawks_postmeta AS mt2 ON (
           wp_posts.ID = mt2.post_id 
           AND mt2.meta_key = '_affiliate_list_73_prio_value_key'
         ) 
       WHERE 
         1 = 1 
         AND (
           wp_casinohawks_term_relationships.term_taxonomy_id IN (73)
         ) 
         AND (
           wp_casinohawks_postmeta.meta_key = '_item_rating_value_key' 
           AND (
             mt1.meta_key = '_affiliate_list_73_prio_value_key' 
             OR mt2.post_id IS NULL
           )
         ) 
         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(mt1.meta_value AS SIGNED) DESC, 
         wp_casinohawks_postmeta.meta_value + 0 DESC 
       LIMIT 
         0, 10
       ```
   
 * Ok so here I changed the WP_Query to sort on “list_no_prio” instead, which is
   the group of posts that don’t have the meta_key I want to sort on. And now it
   works, it’s like WP_Query mixes up the SQL right? Here you can see that in the
   query the correct meta_key is in mt1 but in the LEFT JOIN it is in mt2.
 *     ```
       SELECT 
         SQL_CALC_FOUND_ROWS wp_posts.ID 
       FROM 
         wp_posts 
         LEFT JOIN wp_casinohawks_term_relationships ON (
           wp_posts.ID = wp_casinohawks_term_relationships.object_id
         ) 
         LEFT JOIN wp_casinohawks_postmeta ON (
           wp_posts.ID = wp_casinohawks_postmeta.post_id
         ) 
         LEFT JOIN wp_casinohawks_postmeta AS mt1 ON (
           wp_posts.ID = mt1.post_id
         ) 
         LEFT JOIN wp_casinohawks_postmeta AS mt2 ON (
           wp_posts.ID = mt2.post_id 
           AND mt2.meta_key = '_affiliate_list_73_prio_value_key'
         ) 
       WHERE 
         1 = 1 
         AND (
           wp_casinohawks_term_relationships.term_taxonomy_id IN (73)
         ) 
         AND (
           wp_casinohawks_postmeta.meta_key = '_item_rating_value_key' 
           AND (
             mt1.meta_key = '_affiliate_list_73_prio_value_key' 
             OR mt2.post_id IS NULL
           )
         ) 
         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(mt2.meta_value AS CHAR) DESC, 
         wp_casinohawks_postmeta.meta_value + 0 DESC 
       LIMIT 
         0, 10
       ```
   
 * Any suggestions or if you see something wrong let me know. Thanks in advance,
   this has been a week long issue.

Viewing 5 replies - 1 through 5 (of 5 total)

 *  Moderator [bcworkz](https://wordpress.org/support/users/bcworkz/)
 * (@bcworkz)
 * [4 years, 2 months ago](https://wordpress.org/support/topic/wp_query-produces-wrong-sql/#post-15537213)
 * 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](https://developer.wordpress.org/reference/classes/wp_query/).
 *  Thread Starter [simonlindahl](https://wordpress.org/support/users/simonlindahl/)
 * (@simonlindahl)
 * [4 years, 2 months ago](https://wordpress.org/support/topic/wp_query-produces-wrong-sql/#post-15537638)
 * 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.
 *  Thread Starter [simonlindahl](https://wordpress.org/support/users/simonlindahl/)
 * (@simonlindahl)
 * [4 years, 2 months ago](https://wordpress.org/support/topic/wp_query-produces-wrong-sql/#post-15537793)
 * Hi again [@bcworkz](https://wordpress.org/support/users/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.
 *  Moderator [bcworkz](https://wordpress.org/support/users/bcworkz/)
 * (@bcworkz)
 * [4 years, 2 months ago](https://wordpress.org/support/topic/wp_query-produces-wrong-sql/#post-15540855)
 * 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.
 *  Thread Starter [simonlindahl](https://wordpress.org/support/users/simonlindahl/)
 * (@simonlindahl)
 * [4 years, 2 months ago](https://wordpress.org/support/topic/wp_query-produces-wrong-sql/#post-15547374)
 * Thanks for your reply [@bcworkz](https://wordpress.org/support/users/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..

Viewing 5 replies - 1 through 5 (of 5 total)

The topic ‘WP_Query produces wrong SQL’ is closed to new replies.

## Tags

 * [sql](https://wordpress.org/support/topic-tag/sql/)
 * [wp_query](https://wordpress.org/support/topic-tag/wp_query/)

 * In: [Fixing WordPress](https://wordpress.org/support/forum/how-to-and-troubleshooting/)
 * 5 replies
 * 2 participants
 * Last reply from: [simonlindahl](https://wordpress.org/support/users/simonlindahl/)
 * Last activity: [4 years, 2 months ago](https://wordpress.org/support/topic/wp_query-produces-wrong-sql/#post-15547374)
 * Status: not resolved

## Topics

### Topics with no replies

### Non-support topics

### Resolved topics

### Unresolved topics

### All topics