shinoo

This is what I did this afternoon (roughly) and it works!

add_action( 'pre_get_posts', 'custom_query_vars' );
function custom_query_vars( $query ) {
	if ( !is_admin() && $query->is_main_query()) {
		if ( in_array ( $query->get('post_type'), array('spectacles') ) ) {
			$query->set( 'post_status', 'publish' );
            $query->set( 'meta_key', 'date' );
           	$query->set( 'orderby', 'meta_value_num title');
            $query->set( 'orderby_date_passed_at_the_end', 1 );
		}
	}
	return $query;
}
add_filter( 'posts_clauses', 'sf_orderby_date_passed', 1, 2 );

function sf_orderby_date_passed( $events, $query ) {
	global $wpdb;

	if ( ! $query->get( 'orderby_date_passed' ) && ! $query->get( 'orderby_date_passed_at_the_end' ) ) {
		return $events;
	}

	$orderby = $query->get( 'orderby' );

	if ( is_array( $orderby ) ) {
		if ( ! isset( $orderby['meta_value'] ) && ! isset( $orderby['meta_value_num'] ) ) {
			return $events;
		}
	}
	elseif ( is_string( $orderby ) ) {
		if ( strpos( $orderby, 'meta_value' ) === false ) {
			return $events;
		}
	}
	else {
		return $events;
	}

	if ( ! $meta_key = $query->get( 'meta_key' ) ) {
		return $events;
	}

	$meta_table = $query->meta_query->meta_table;
	$meta_key   = esc_attr( $meta_key );

	if ( ! preg_match( '@(' . $meta_table . '|mt\d+)\.meta_key\s*=\s*\'' . $meta_key . '\'@', $events['where'], $meta_prefix ) ) {
		return $events;
	}
	$meta_prefix = $meta_prefix[1];

	$meta_prefix2 	= "mt1";
	$meta_key2 		= "_additional_settings";

	// Remove the meta_key statement.
	$events['where'] = preg_replace(
		'@AND\s*\(\s*' . $meta_prefix2 . '\.meta_key\s*=\s*\'' . $meta_key2 . '\'\s*\)@',
		'',
		$events['where']
	);

	// Add the meta_key statement and change the INNER JOIN to LEFT JOIN.
	$events['join'] = str_replace(
		'INNER JOIN ' . $meta_prefix . ' ON ( ' . $wpdb->posts . '.ID = ' . $meta_prefix . '.post_id )',
		'LEFT JOIN '  . $meta_prefix . ' ON ( ' . $wpdb->posts . '.ID = ' . $meta_prefix . '.post_id AND ' . $meta_prefix . '.meta_key = \'' . $meta_key . '\' )',
		$events['join']
	);

	if ( $query->get( 'orderby_date_passed_at_the_end' ) ) {
		$events['orderby'] = 'CASE 
			WHEN '.$meta_prefix.'.meta_value > Cast(now() AS DATE) THEN 1 
			WHEN '.$meta_prefix.'.meta_value < Cast(now() AS DATE) THEN -1 
			ELSE 0
			END DESC, 
			case when '.$meta_prefix.'.meta_value < Cast(now() AS DATE) then '.$meta_prefix.'.meta_value end desc,
			case when '.$meta_prefix.'.meta_value > Cast(now() AS DATE) then '.$meta_prefix.'.meta_value end asc
		';
	}
	return apply_filters( 'sf_orderby_date_passed_clauses', $events, $query, $meta_key, $meta_prefix );
}

The subject is therefore resolved 🙂

Thank you for your help!

I found an equivalent in pure sql:

ORDER  BY CASE 
            WHEN eventdate = Cast(now() AS DATE) THEN 1 
            WHEN eventdate < Cast(now() AS DATE) THEN -1 
            ELSE 0
          END DESC, 
          case when eventdate < Cast(now() AS DATE) then eventdate end desc,
          case when eventdate > Cast(now() AS DATE) then eventdate end asc

But I don’t see how it can be adapted …