order posts on meta_query with relation

I solved the problem thanks to this post by keesiemeijer

my query now looks like this:

add_filter( 'posts_where', 'start_date_posts_where' );

	$agenda_loop = new WP_Query( array(
	    'cat'   		=> $thisCatID,
	    'paged'			=> $paged,
	    'meta_key'   	=> 'dateFrom',
	    'orderby'     	=> 'meta_value',
	    'order'     	=> 'ASC'
	) );

	//echo $agenda_loop->request;

	remove_filter( 'posts_where', 'start_date_posts_where' );

I added the following function to my functions.php

function start_date_posts_where( $where ) {
	global $wpdb;

	$today = date('Y-m-d');

	$where = "
		AND  ($wpdb->postmeta.meta_key = 'dateTo'
		AND CAST($wpdb->postmeta.meta_value AS DATE) >= '$today')
		OR   ($wpdb->postmeta.meta_key = 'dateFrom'
		AND CAST($wpdb->postmeta.meta_value AS DATE) >= '$today')
	";
	return $where;
}

This results in the following query:

SELECT SQL_CALC_FOUND_ROWS cc_posts.ID FROM cc_posts INNER JOIN cc_term_relationships ON (cc_posts.ID = cc_term_relationships.object_id) INNER JOIN cc_postmeta ON (cc_posts.ID = cc_postmeta.post_id) WHERE 1=1 AND (cc_postmeta.meta_key = 'dateTo' AND CAST(cc_postmeta.meta_value AS DATE) >= '2014-05-24') OR (cc_postmeta.meta_key = 'dateFrom' AND CAST(cc_postmeta.meta_value AS DATE) >= '2014-05-24') GROUP BY cc_posts.ID ORDER BY cc_postmeta.meta_value ASC LIMIT 0, 10

This is close to what I want, only thing that bugs me is that posts with meta_key dateTo get sorted by dateTo instead of dateFrom.

Can you provide me with some dates for the custom fields so I can reproduce the issue.
e.g. for example

post A - dateFrom = 2014-05-27
post B - dateFrom = 2014-05-25, dateTo = 2014-05-30
etc..

Also provide the expected order.

post A - dateFrom = 2014-06-05
post B - dateFrom = 2014-05-17, dateTo = 2014-06-28
post C - dateFrom = 2014-06-02

I expect the order to be B-C-A.

Currently it is C-A-B. It looks as though ORDERBY is using dateTo meta_value when present. I tried a lot of methods with the same result. Thank you for your time!

Filter only the meta (where clause) of the query with the filter hook ‘get_meta_sql’. Try it with this as your query:

<?php
add_filter( 'get_meta_sql', 'get_meta_sql_date' );

$today = date( 'Y-m-d' );

$args = array(
	'ignore_sticky_posts' => true,
	'cat'                 => $thisCatID,
	'paged'               => $paged,
	'meta_key'            => 'dateFrom',
	'orderby'             => 'meta_value',
	'order'               => 'ASC',

	// produces meta join and where clauses for the query
	// which will be filtered in functions.php
	'meta_query' => array(
		'relation' => 'OR',
		array(
			'key'     => 'dateFrom',
			'value'   => $today,
			'compare' => '>=',
			'type'    => 'DATE'
		),
		array(
			'key'     => 'dateTo',
			'value'   => $today,
			'compare' => '>=',
			'type'    => 'DATE'
		),
	)
);

$agenda_loop = new WP_Query( $args );
remove_filter( 'get_meta_sql', 'get_meta_sql_date' );
?>

And this in your functions.php file:

function get_meta_sql_date( $pieces ) {
	global $wpdb;

	$query = " AND $wpdb->postmeta.meta_key = 'dateFrom'
		AND (mt1.meta_key = 'dateFrom' OR mt1.meta_key = 'dateTo')
		AND CAST(mt1.meta_value AS DATE) >= %s";

	$pieces['where'] = $wpdb->prepare( $query, date( 'Y-m-d' ) );

	return $pieces;
}

Keesiemeijer thank you so much for that code..

I’ve been googling and struggling with this for two days and NO WHERE did I find a solution. I adjusted your example for my specific instance and this worked like a charm first try. I am in your debt!

Youtag, thank you as well for posting pretty much the EXACT scenario I was having problems with in order to prompt Keesie’s response.

Thanks so much.

Thank you Keesiemeijer. I did not yet try your solution but I learned so much from you already. I’m sure it will be useful for other folks as this is a common problem when dealing with custom posts as events.

61pixels, thank you for confirming that it works. Feel free to post your specific code.

Yes, Just created an account to thank this thread, saved me hours of life!