WordPress - Order by ACF date, date first, then empty fields last

Question

I'm trying to order content by the dick picker provided by ACF.

• order by date field
  • Dates in order of soonest(past/present) to farthest out (ASC ordering).
  • Then any post that meets the criteria but doesn't have a date set would be last.

I have the following Query arguments:

(
    [post_type] => people
    [posts_per_page] => 10
    [paged] => 1
    [meta_query] => Array
        (
            [0] => Array
                (
                    [key] => has_scheduling
                    [value] => 1
                )

            [1] => Array
                (
                    [key] => custom_ID
                    [compare] => !=
                    [value] => 
                )

            [2] => Array
                (
                    [key] => date_picker
                    [compare] => !=
                    [value] => 
                )

        )

    [tax_query] => Array
        (
            [0] => Array
                (
                    [taxonomy] => people_types
                    [field] => term_id
                    [terms] => 153
                )

        )

    [meta_key] => date_picker
    [orderby] => meta_value_num
    [order] => ASC
)

I noticed that this Query will only load the first part correctly. It grabs content based on all the parameters, then orders is by date, but it excludes the content that doesn't have a date set.

I've tried this as well:

(
    [post_type] => people
    [posts_per_page] => 10
    [paged] => 1
    [meta_query] => Array
        (
            [0] => Array
                (
                    [key] => has_scheduling
                    [value] => 1
                )

            [1] => Array
                (
                    [key] => custom_ID
                    [compare] => !=
                    [value] => 
                )

            [2] => Array
                (
                    [relation] => OR
                    [0] => Array
                        (
                            [key] => date_picker
                            [compare] => EXISTS
                        )

                    [1] => Array
                        (
                            [key] => date_picker
                            [compare] => NOT EXISTS
                        )

                )

        )

    [tax_query] => Array
        (
            [0] => Array
                (
                    [taxonomy] => people_types
                    [field] => term_id
                    [terms] => 153
                )

        )

    [orderby] => meta_value
    [order] => ASC
)

So here I don't exclude empty fields, I check if it exists and then doesn't. Then order by the data again. I also switched to use meta_value instead of meta_value_num

Any tips or tricks, or if you have run into issue as well, I'd love to see what you did.

Answer 1

For anyone also trying to do the same, the end result for this ended using a custom a orderby filter. Example code below.

// Order By custom date.
add_filter('posts_orderby', function (string $orderby, WP_Query $query) {
    if ($query->get('orderby') === '__custom_date__') {
        $orderby = "mta.meta_value is NULL, mta.meta_value ='', mta.meta_value ASC";
    }
    return $orderby;
}, 10, 2);

// Extra join to order by custom date.
add_filter('posts_join', function (string $join, WP_Query $query) {
    if ($query->get('orderby') === '__custom_date__') {
        $join .= " LEFT JOIN wp_postmeta AS mta ON ( wp_posts.ID = mta.post_id AND mta.meta_key = 'date_picker')";
    }
    return $join;
}, 10, 2);

This is then used as a parameter that gets passed to orderby

$args['orderby'] = '__custom_date__';