Orderby custom date fields with meta_query broken on OR relation

  • I have an ‘event’ post type that has custom start and end date fields. In a block on the home page, I’m trying to show only upcoming or ongoing events. So, to achieve this, I’m doing a meta_query that compares the two date fields to today’s date with the intent of showing events with start or end date equal to today’s date or later. After getting the returned events, I’m sorting by the start date. One important note, some events have end dates, while some are merely one day events with just a start date.

    <?php /* Start the loop */
$args = array(
	'post_status' => 'publish',
	'post_type' => 'event',
	'posts_per_page' => 5,
	'meta_key' => 'x_start_date',
	'orderby' => 'meta_value',
	'order' => 'ASC',
	'meta_query' => array(
		'relation' => 'OR',
		array(
			'key' => 'x_start_date',
			'value' => date('Y-m-d'),
			'compare' => '>=',
			'type' => 'DATE'
		),
		array(
			'key' => 'x_end_date',
			'value' => date('Y-m-d'),
			'compare' => '>=',
			'type' => 'DATE'
		),
	)
);
$my_query = new WP_Query( $args ); ?>
<?php while ( $my_query->have_posts() ) : $my_query->the_post(); ?>

    The query works but the ‘orderby’ is broken. If I take the second meta_query (end date) out of the equation and remove the ‘OR’ relation, the sort works perfectly. So the orderby statement is clearly failing when the second meta_query is added.

    Does anyone have any ideas what I might be doing wrong? Any help is greatly appreciated!

Viewing 4 replies - 1 through 4 (of 4 total)
  • Thread Starter Sean M.

    (@seankmchenry)

    For reference, the generated SQL is:

    string(640) "SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id) INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id) WHERE 1=1 AND wp_posts.post_type = 'event' AND (wp_posts.post_status = 'publish') AND (wp_postmeta.meta_key = 'x_start_date' OR (mt1.meta_key = 'x_start_date' AND CAST(mt1.meta_value AS DATE) >= '2014-02-24') OR (mt2.meta_key = 'x_end_date' AND CAST(mt2.meta_value AS DATE) >= '2014-02-24') ) GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value ASC LIMIT 0, 5"

    Moderator keesiemeijer

    (@keesiemeijer)

    I did not test this, but give this a shot. Query like this:

    add_filter('posts_orderby', 'start_date_posts_orderby');
$my_query = new WP_Query( $args );
remove_filter('posts_orderby', 'start_date_posts_orderby');

    And put this in your theme’s functions.php:

    function start_date_posts_orderby($orderby) {
	global $wpdb;
	$orderby = "mt1.meta_key ASC";
	return $orderby;
}

    btw:
    consider creating a child theme instead of editing your theme directly – if you upgrade the theme all your modifications will be lost.

    Moderator keesiemeijer

    (@keesiemeijer)

    This can’t be done with a normal query. Remove the code I posted before and try it with this in your theme’s functions.php:

    function start_date_posts_where( $where ) {
	global $wpdb;

	$query = "AND $wpdb->posts.post_type = 'event' AND ($wpdb->posts.post_status = 'publish')
		AND $wpdb->postmeta.meta_key = 'x_start_date'
		AND (mt1.meta_key = 'x_start_date' OR mt1.meta_key = 'x_end_date')
		AND CAST(mt1.meta_value AS DATE) >= %s";

	$new_where = $wpdb->prepare( $query, date( 'Y-m-d' ) );

	// replace WHERE sql with new one
	return $new_where;
}

    and Query like this in your template:

    add_filter( 'posts_where', 'start_date_posts_where' );
$my_query = new WP_Query( $args );
remove_filter( 'posts_where', 'start_date_posts_where' );

    Note: This only works for the current query arguments (in $args array) as the WHERE sql is completely replaced by a new one to get the correct order.

    I had the exact same problem, and I came close to the solution thanks to keesiemeijer’s idea. Only thing bugging is that posts with end_date now get sorted by end_date instead of start_date. Perhaps this will help you.

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘Orderby custom date fields with meta_query broken on OR relation’ is closed to new replies.