Display upcoming events then past events

You can only set “orderby” to values outlined on the docs page. Your only real choice is “meta_value_num” for numeric values. But this requires “meta_key” be set, which will mess up your “meta_query” arguments.

Why couldn’t you set “meta_key” as “date” instead of doing a meta query for both earlier and later than $today? It should amount to the same results, then ordering by meta_key_val will work. This assumes a numeric sorting of dates will work correctly. Dates either need to be saved as timestamps or in yyyy-mm-dd format. Doing dd-mm-yyyy or similar will not order correctly.

Hi,

I make a request before and after today because I want to display first the events that happen from the closest to the farthest today then the events passed from the closest to the farthest today (so the vice versa).

So 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 …

Use the posts_orderby filter to insert your own clause into the eventual SQL. You should examine the entire SQL request passed in the “posts_request” filter so you can properly integrate your clause into the SQL.

Both filters also pass the entire query object so you can still check that it’s a event query being processed so you don’t inadvertently modify the wrong query.

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!