Support » Fixing WordPress » order posts on meta_query with relation
order posts on meta_query with relation
-
[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 theirdateTo
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' => '>=', ), ), ) );
-
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 bydateTo
instead ofdateFrom
.Can you provide me with some dates for the custom fields so I can reproduce the issue.
e.g. for examplepost 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!
- The topic ‘order posts on meta_query with relation’ is closed to new replies.