Order a WP_Query by a future date, with posts without a date at the bottom

<?php

// Query arguments ready to use in a Listing Builder lising
// Orders 'courses' posts by a future date in the 'offer_date_1' ACF Date Picker field, with the earliest date first (ASC),
// then showing posts without the field or without a date in the field, at the bottom.
// Includes a 'offer_date_query' flag to be used in the 'posts_clauses' function below,
// which is needed to show posts with a date before the posts without one.
// If you're using an ACF Date Time Picker field instead of a Date Picker field, see the second example here:
// https://facetwp.com/help-center/using-facetwp-with/advanced-custom-fields/#order-a-listing-by-a-date-time-picker-field

return [
    'post_type'      => [
        'courses'
    ],
    'post_status'    => [
        'publish'
    ],
    'posts_per_page' => 50,
	
	'offer_date_query' => true, // Needed for identifying in 'posts_clauses' hook

    // Order by date and fallback order	
    'meta_key'       => 'offer_date_1',
    'orderby'        => array(
        'meta_value_num' => 'ASC', // ASC = Smallest number (earliest date) first
        'date'           => 'DESC', // Fallback order if offer_date_1 the same or not exists: post_date (newest first). Adapt as needed.
    ),

    // Only retrieve posts with a date in the future or no date at all
    'meta_query'     => array(
		
        'relation' => 'OR', // Allow multiple conditions
		
        // Get posts with a future date
        array(
            'key'     => 'offer_date_1',
            'value'   => current_time('Ymd'),
            'compare' => '>',
            'type'    => 'NUMERIC',
        ),
				
        // Get posts with no date field
        array(
            'key'     => 'offer_date_1',
            'compare' => 'NOT EXISTS', // Posts without this field
        ),
		
		// Get posts with no date value
        array(
            'key'     => 'offer_date_1',
			'value'   => '',
            'compare' => '=',
        ),
		
    ),

];


// Get the 'offer_date_query' query above to order
// posts with an date first, the rest after 
// Add this to your functions.php
add_filter( 'posts_clauses', function( $clauses, $query ) {
    if ( ! $query->get( 'offer_date_query' ) ) { // the flag in the query arguments above
        return $clauses;
    }

    global $wpdb;

    // Join postmeta explicitly to have a stable alias for ordering
    // If you already have a JOIN for offer_date_1 via meta_query,
    // this will effectively be a no-op because of identical JOIN.
    $clauses['join'] .= $wpdb->prepare(
        " LEFT JOIN {$wpdb->postmeta} AS od1
          ON od1.post_id = {$wpdb->posts}.ID
         AND od1.meta_key = %s",
        'offer_date_1'
    );

    // 1) CASE: posts *with* non-empty offer_date_1 get sort key 0
    //         posts *without* (NULL or '') get sort key 1
    // 2) Then order by the numeric value of offer_date_1 ascending
    // 3) Finally, fallback to post_date DESC

    $clauses['orderby'] = "
        CASE
            WHEN od1.meta_value IS NOT NULL AND od1.meta_value != '' THEN 0
            ELSE 1
        END ASC,
        CAST(od1.meta_value AS UNSIGNED) ASC,
        {$wpdb->posts}.post_date DESC
    ";

    return $clauses;
}, 10, 2 );