Support » Fixing WordPress » order posts on meta_query with relation

  • Resolved youtag

    (@youtag)


    [Moved from Your WordPress which is NOT a support forum.]

    I am trying to list posts in my agenda, ordered by meta_key dateFrom.

    The following code works correctly:

    $agenda_loop = new WP_Query( array(
    	    'cat'   		=> $thisCatID,
    	    'paged'		=> $paged,
    	    'meta_key'   	=> 'dateFrom',
    	    'orderby'     	=> 'meta_value',
    	    'order'       	=> 'ASC',
    	    'meta_query' => array(
    	        array(
    	            'key'		=> 'dateFrom',
    	            'value'		=> date('Y-m-d'),
    	            'compare'		=> '>=',
    	        ),
    	    ),
    	) );

    However, if I add a condition (I also want to list events whose dateFrom is in the past, if their dateTo is set and in the future), the order is not working anymore:

    $agenda_loop = new WP_Query( array(
    	    'cat'   		=> $thisCatID,
    	    'paged'		=> $paged,
    	    'meta_key'   	=> 'dateFrom',
    	    'orderby'     	=> 'meta_value',
    	    'order'       	=> 'ASC',
    	    'meta_query'	=> array(
    
    	    	'relation' 	=> 'OR',
    
    	        array(
    	            'key'		=> 'dateFrom',
    	            'value' 		=> date('Y-m-d'),
    	            'compare' 			=> '>=',
    	        ),
    	        array(
    	            'key'		=> 'dateTo',
    	            'value'		=> date('Y-m-d'),
    	            'compare'	=> '>=',
    	        ),
    	    ),
    	) );
Viewing 7 replies - 1 through 7 (of 7 total)
  • Thread Starter youtag

    (@youtag)

    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.

    Moderator keesiemeijer

    (@keesiemeijer)

    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.

    Thread Starter youtag

    (@youtag)

    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!

    Moderator keesiemeijer

    (@keesiemeijer)

    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.

    Thread Starter youtag

    (@youtag)

    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!

Viewing 7 replies - 1 through 7 (of 7 total)
  • The topic ‘order posts on meta_query with relation’ is closed to new replies.