Improve docs for orderby meta clause in meta_query

[dariopad]

Hi, I'm trying to get results ordered by meta clauses and post date in a WP_Query with custom fields meta_query. It should work from 4.2 on but it doesn't for me. Any suggestion on the query below would be appreciated:

Is that a bug?

$args = array ( [...],
  'meta_query' => array(
       'relation' => 'OR'
       'my_clause' => array(
               array( 'key' => 'any_key', 'compare' => '=', 'value' => '1', 'type' => 'CHAR')
               array( 'key' => 'another_key', 'compare' => '=', 'value' => '1', 'type' => 'CHAR')
           ),
        'another_clause' => array(
               array( 'key' => 'any_other_key', 'compare' => '=', 'value' => '1', 'type' => 'CHAR')
            )
    ),
    'orderby' => array('my_clause'=>'DESC', 'post_date'=>'DESC')
 );

References:
​https://make.wordpress.org/core/2015/03/30/query-improvements-in-wp-4-2-orderby-and-meta_query/
​https://make.wordpress.org/core/2014/08/29/a-more-powerful-order-by-in-wordpress-4-0/

Use Case:
​http://www.glocalmart.it/

[boonebgorges]

Can you share more details about what you're seeing, and what you expect to see?

My guess is that it's not working because 'my_clause' is not a first-order clause. The SQL for that section looks something like this:

  ...OR (
    ( mt2.key = 'any_key' AND mt2.value = '1' )
    AND
    ( mt3.key = 'another_key' AND mt3.value = '1' )
  )

As such, when the ORDER BY clause for 'my_clause' is built, WP_Meta_Query is unsure whether to use mt2 or mt3.

Any more details you could provide would be helpful.

[dariopad]

Now that you pointed out this example I realize that I really misunderstood the use of this improvement. :(
I realize now that this improvement relates to a way to reference a key in the meta_query and include it to the orderby clause. Is that what the improvement means?
So as per your example, the only clause that should work on my previous example would be the 'another_key' clause as it reference a single meta_key?

[boonebgorges]

Replying to dariopad:

Now that you pointed out this example I realize that I really misunderstood the use of this improvement. :(
I realize now that this improvement relates to a way to reference a key in the meta_query and include it to the orderby clause. Is that what the improvement means?
So as per your example, the only clause that should work on my previous example would be the 'another_key' clause as it reference a single meta_key?

As your query is written? Yes, 'another_clause' is the only one that will work. However, you could do this:

$args = array ( [...],
  'meta_query' => array(
       'relation' => 'OR'
       'my_clause' => array(
               'my_subclause_1' => array( 'key' => 'any_key', 'compare' => '=', 'value' => '1', 'type' => 'CHAR')
               'my_subclause_2' => array( 'key' => 'another_key', 'compare' => '=', 'value' => '1', 'type' => 'CHAR')
           ),
        'another_clause' => array(
               array( 'key' => 'any_other_key', 'compare' => '=', 'value' => '1', 'type' => 'CHAR')
            )
    ),
    'orderby' => array('my_subclause_1'=>'DESC', 'post_date'=>'DESC')
 );

and that should order by the "my_subclause_1" table. (However, note that because you're querying for value=1 in all cases, there'll always be a tie, which means it'll always fall back to post_date. Clause-specific 'orderby' works best if you aren't using 'compare' => '='.)

It sounds to me like this is all probably working as intended. If you have suggestions for how to improve the documentation, or if you think there is indeed a bug, please feel free to reopen the ticket. Thanks!

[dariopad]

Replying to boonebgorges:

Replying to dariopad:

Now that you pointed out this example I realize that I really misunderstood the use of this improvement. :(
I realize now that this improvement relates to a way to reference a key in the meta_query and include it to the orderby clause. Is that what the improvement means?
So as per your example, the only clause that should work on my previous example would be the 'another_key' clause as it reference a single meta_key?

As your query is written? Yes, 'another_clause' is the only one that will work. However, you could do this:

$args = array ( [...],
  'meta_query' => array(
       'relation' => 'OR'
       'my_clause' => array(
               'my_subclause_1' => array( 'key' => 'any_key', 'compare' => '=', 'value' => '1', 'type' => 'CHAR')
               'my_subclause_2' => array( 'key' => 'another_key', 'compare' => '=', 'value' => '1', 'type' => 'CHAR')
           ),
        'another_clause' => array(
               array( 'key' => 'any_other_key', 'compare' => '=', 'value' => '1', 'type' => 'CHAR')
            )
    ),
    'orderby' => array('my_subclause_1'=>'DESC', 'post_date'=>'DESC')
 );

and that should order by the "my_subclause_1" table. (However, note that because you're querying for value=1 in all cases, there'll always be a tie, which means it'll always fall back to post_date. Clause-specific 'orderby' works best if you aren't using 'compare' => '='.)

It sounds to me like this is all probably working as intended. If you have suggestions for how to improve the documentation, or if you think there is indeed a bug, please feel free to reopen the ticket. Thanks!

Wow! Now that I better understood what's the improvement was about, I can state for sure there is no bug and your last example fits what I was trying to reach out. I didn't realize I could also use a sub-clause in the meta query. This is indeed the most powerful improvement in meta query ever.
Really thank you for your precious support and I apologize if I didn't catch the real meaning of the improvement.
I just suggest to better document this powerful improvement with some extra example as you shared with me in this ticket.
Kr, Dario.

[johnbillion]

Re-opening for docs improvements. Suggestions/patch welcome.

[DrewAPicture]

In 37688:

Docs: Improve first-order clause documentation for the $meta_query parameter in the constructor for WP_Meta_Query.

First-order meta query clauses are defined as clauses that have either a 'key' or 'value' array key. When using named first-order clauses in meta queries to order results in the parent query, WP_Meta_Query can additionally accept first-order clauses at the sub-clause level, which was not previous documented.

Fixes #32659.