Order by meta_key doesn't work

Question

I am using this code but it seems that ordering by meta_keys br_category first and br_name second, doesn't produce the correct results

$args = array(
   'post_type' => 'brands',
   'meta_query' => array( 
      array( 
        'key' => 'br_type', 
        'value' => 'Aviation'
      ), 
      array( 
        'key' => 'br_category' 
      ), 
      array( 
        'key' => 'br_name' 
      ) 
   ), 
   'posts_per_page' => -1, 
   'orderby' => [ 'br_category' => 'ASC', 'br_name' => 'ASC' ], 
   'order' => 'ASC', 
   'fields' => 'ids'
);

Is the syntax I am using incorrect?

Answer 1

Current Query

$args = array(
   'post_type' => 'brands',
   'meta_query' => array( 
      array( 
        'key' => 'br_type', 
        'value' => 'Aviation'
      ), 
      array( 
        'key' => 'br_category' 
      ), 
      array( 
        'key' => 'br_name' 
      ) 
   ), 
   'posts_per_page' => -1, 
   'orderby' => [ 'br_category' => 'ASC', 'br_name' => 'ASC' ], 
   'order' => 'ASC', 
   'fields' => 'ids'
);

produce this SQL query

'SELECT wp_posts.ID FROM wp_posts  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 )  INNER JOIN wp_postmeta AS mt2 ON ( wp_posts.ID = mt2.post_id ) WHERE 1=1  
    AND ( 
        ( wp_postmeta.meta_key = \'br_type\' AND wp_postmeta.meta_value = \'Aviation\' ) 
    AND 
    mt1.meta_key = \'br_category\' 
    AND 
    mt2.meta_key = \'br_name\'
    ) AND wp_posts.post_type = \'brands\' AND (wp_posts.post_status = \'publish\' OR wp_posts.post_status = \'acf-disabled\' OR wp_posts.post_author = 1 AND wp_posts.post_status = \'private\') GROUP BY wp_posts.ID  '

It means it does not add any order by clause because it does not understand.

Here is the correct way to order by multiple meta key value

$args = array(
        'post_type' => 'brands',
        'meta_query' => array( 
            // 'relation' => 'AND', // default is AND
            'br_type_clause' => array( 
                'key' => 'br_type', 
                'value' => 'Aviation',
            ), 
            'br_category_clause' => array( 
                'key' => 'br_category',
            ), 
            'br_name_clause' => array( 
                'key' => 'br_name',
            ) 
        ), 
        'posts_per_page' => -1, 

        'meta_key' => array( 'br_category', 'br_name', 'br_type' ),
        // add sql: wp_postmeta.meta_key IN ('br_category','br_name','br_type') 

        'orderby' => array(
            'br_type_clause' => 'ASC', 
            'br_category_clause' => 'ASC',
            'br_name_clause' => 'ASC',
            'name' => 'ASC', // illustrative purpose, could use post key
        ), 
        // 'order' => 'ASC', // it is ignored with above orderby override
        'fields' => 'ids'
    ),

which produce the SQL

SELECT   wp_posts.ID FROM wp_posts  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 )  INNER JOIN wp_postmeta AS mt2 ON ( wp_posts.ID = mt2.post_id )  INNER JOIN wp_postmeta AS mt3 ON ( wp_posts.ID = mt3.post_id ) WHERE 1=1  AND ( 
  wp_postmeta.meta_key IN ('br_category','br_name','br_type') 
  AND 
  ( 
    ( mt1.meta_key = 'br_type' AND mt1.meta_value = 'Aviation' ) 
    AND 
    mt2.meta_key = 'br_category' 
    AND 
    mt3.meta_key = 'br_name'
  )
) AND wp_posts.post_type = 'brands' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled' OR wp_posts.post_author = 1 AND wp_posts.post_status = 'private') GROUP BY wp_posts.ID ORDER BY CAST(mt1.meta_value AS CHAR) ASC, CAST(mt2.meta_value AS CHAR) ASC, CAST(mt3.meta_value AS CHAR) ASC, wp_posts.post_name ASC 

Reference: WP_Query with section ‘orderby’ with multiple ‘meta_key’s

Answer 2

Have you tried:

'orderby' => array( 'br_category' => 'ASC', 'br_name' => 'ASC' ),