wp_query Orderby multiple meta doesnt work

• tatof

• January 18, 2023 at 6:01 am

Hello,

I’ve got two ACF fields I want to order.
1. True/False field “activi_datumperiode” (qw2)
2. Date field “activi_datum” (qw1)

I want the query to be ordered by qw2 first. All items with value 0 at the bottem (DESC).
Then I want all the dates qw1 be orderd ASC.

So it would be
– Post with date 01-01-2023
– Post with date 02-02-2023
– Post without date (value 0)

I can’t seem to fix this.. I think maybe because date field exists (empty) in every post so thats ASC the lowest. But can’t seem to figure this out.

Any clue whats wrong?

function custom_query_vars( $query ) {

    if ( !is_admin() && $query->is_main_query() ) {

        // advies only parents && alles geen paginatie
        if ( is_post_type_archive() == 'activiteiten' ) {
            $query->set( 'post_parent', 0 );
            $query->set( 'posts_per_page', 20 );

            $meta_query = array(
                'relation' => 'OR',
                'qw1' => array(
                    array(
                        'key' => 'activi_datum',
                        'value' => date('Ymd'),
                        'type' => 'DATE',
                        'compare' => '>='
                    ),
                    array(
                        'key' => 'activi_datumperiode',
                        'value' => 1,
                        'compare' => '='
                    ),
                ),
                'qw2' => array(
                    'key' => 'activi_datumperiode',
                    'value' => 0,
                    'compare' => '='
                ),
            );
            $query->set( 'meta_query', $meta_query );

            $query->set( 'orderby', array(
                'qw2' => 'DESC',
                'qw1' => 'ASC',
            ) );
            //$query->set( 'order', 'ASC' );
        }
    }
    return $query;
}
add_action( 'pre_get_posts', 'custom_query_vars' );

• John Huebner

• January 19, 2023 at 11:38 am

Query clauses have a few issues that are not documented and you only find them by finding theme.

Query clauses do not work well with nested meta queries. Honestly, I don’t remember if they work at all with nested meta queries.

Query clauses 100% do not work with a nested query that has multiple meta keys associated with it.

// order by true/false field, desc and then date field asc
$meta_query = array(
    'relation' => 'AND',
    'qw1' => array(
        // it does not matter what value this field is
        // it is only used for ordering
        'key' => 'activi_datumperiode',
        'compare' => 'EXISTS'
    ),
    'qw2' => array(
      // note that ACF fields not stored as dates in DB
      // they are text values (or number)
      'key' => 'activi_datum',
      'value' => date('Ymd'),
      'compare' => '>='
    ),
);

$orderby = array('qw1' => 'DESC', 'qw2' => 'ASC');

• tatof

• January 19, 2023 at 11:49 am

Thanks I think I allready tried this but without the compare “exists”.
I’m abroad right now, will try it out next week. Thank you for your help! 🙂

• Aaradhya1700

• January 21, 2023 at 7:31 am

thanks

• tatof

• February 13, 2023 at 7:21 am

Sorry for the long wait.. tried this code but doesnt work correctly.

The “activi_datum” is empty for the post that doesnt have “activi_datumperiode” enabled. Its a conditional field.

If “activi_datumperiode” turned on == enable “activi_datum” field.
If “activi_datumperiode” turned off == enable “free text” field.

What your code outputs is an ASC date list without the posts without a date.
Simply changing the relation “AND” in “OR” fixes the issue only orderby Date doenst work anymore.

22-03-2023
22-02-2023
28-03-2023
28-06-2023
11-05-2023
Every monday
Every Sunday

Looks like it orders by “activi_datumperiode” and then by post creation date

• John Huebner

• February 13, 2023 at 12:45 pm

You cannot order by a meta field in WP if every post does not have a value that is order-able. Posts without values will be ignored. Posts with values, but are empty (i.e. empty string) will either be ordered first or last depending on the order you want.

• tatof

• February 14, 2023 at 5:10 am

Yes thats why I made two query clauses in the first place…

So what I want is impossible. The date field will always be empty if the user chooses to set the Conditional field to “no date”.

Possible solution:
Then I have to make a function that triggers on post save that will fill the date field with dummy data so its filled?

So something like: (not correct code i know)

onpost save{
if(!get_field('activi_datumperiode')){ set activi_datum = 1}
}