Fetch posts by meta_value and order by the same? WordPress
- SOLVED
I'm creating an event site, and have made it so that each event post can have multiple event dates, which are stored as several meta data records in wp_postmeta, like so:
meta_id post_id meta_key meta_value
248 58 event_start_date 2017-03-06
249 58 event_start_date 2017-03-08
250 58 event_start_date 2017-03-10
251 58 event_start_date 2017-03-11
252 58 event_start_date 2017-03-14
... ... ... ...
Notice that this example is showing the different start dates for one particular post with the post_id of 58.
Let's say that today's date is 2017-03-07. How can I query so that it fetches posts with the earliest start date that hasn't yet 'expired' (2017-03-08 in this case), and that, at the same time, posts get ordered by this earliest start date as well (2017-03-08)? In this case, I'd like this particular post to appear between posts which earliest start dates (that haven't expired yet) are, for example, 2017-03-07 and 2017-03-09, and so on.
This is what I have so far in a 'pre_get_posts' hooked function for fetching and ordering the posts, but it doesn't give me the result I want and I have currently no idea how to achieve it:
$query->set( 'meta_key', 'event_start_date' );
$query->set( 'meta_query', array(
array(
'key' => 'event_start_date',
'value' => date('Y-m-d'),
'compare' => '>=',
'type' => 'DATE'
),
));
$query->set( 'orderby', 'meta_value' );
$query->set( 'order', 'ASC' );
-----------------------
EDIT: I thought it would work with something like this:
$query->set( 'post_type', array('arrangement', 'bundle'));
$query->set( 'meta_query', array(
'date_clause' => array(
'key' => 'event_start_date',
'value' => date('Y-m-d'),
'compare' => '>=',
'type' => 'DATE',
),
$query->set( 'orderby', 'date_clause' );
$query->set( 'order', 'ASC' );
));
..from this page: https://make.wordpress.org/core/2015/03/30/query-improvements-in-wp-4-2-orderby-and-meta_query/
But I can't get that to work either, so I might have misunderstood the purpose of that approach? Am I on the right track here?
Answers (4)
Hai Bui answers:
I found the problem. It's because you set the meta key twice. Once with
$query->set( 'meta_key', 'event_start_date' );
and once inside the meta_query array:
$query->set( 'meta_query', array(
array(
'key' => 'event_start_date',
'value' => date('Y-m-d'),
'compare' => '>=',
'type' => 'DATE'
),
));
If you remove the first line, it will work. Remove this line
$query->set( 'meta_key', 'event_start_date' );
Let me know if there is still any problem.
User161833 comments:
I have tried that before as well. The problem is that then it orders posts by the last date (i.e. 2017-03-14 in this case). I need posts to be ordered by the first date that hasn't yet expired (2017-03-08).
Hai Bui comments:
I see. You can fix it by adding:
add_filter('posts_orderby', 'custom_events_orderby');
function custom_events_orderby($orderby_statement) {
if (!is_admin()) {
$orderby_statement = "MIN(wp_postmeta.meta_value) ASC";
}
return $orderby_statement;
}
Hai Bui comments:
add_filter('posts_orderby', 'custom_events_orderby');
function custom_events_orderby($orderby_statement) {
global $wpdb;
if (!is_admin()) {
$orderby_statement = "MIN($wpdb->postmeta.meta_value) ASC";
}
return $orderby_statement;
}
Hai Bui comments:
Have you tried this?
User161833 comments:
Yes, I just tried this, but it still doesn't seem to really achieve what I want, as it still orders by the last start date. So a post with two start dates of say 2017-03-20 and 2017-03-25 gets ordered after a post with a start date of say 2017-03-24.
So this is what I have so far:
$query->set( 'post_type', array('arrangement', 'bundle'));
$query->set( 'meta_query', array(
array(
'key' => 'event_start_date',
'value' => date('Y-m-d'),
'compare' => '>=',
'type' => 'DATE'
),
));
...and...
add_filter('posts_orderby', 'custom_events_orderby');
function custom_events_orderby($orderby_statement) {
global $wpdb;
if (!is_admin()) {
$orderby_statement = "MIN($wpdb->postmeta.meta_value) ASC";
}
return $orderby_statement;
What does this last piece of code do more specifically?
Hai Bui comments:
the last piece is the custom order statement for the query so that the results are order by the "MINimum" value of the meta_value ( = the earliest day after start day). I've tested it already, it works for me. I think you need to add the 'ASC' order as well.
$query->set( 'order', 'ASC' );
Francisco Javier Carazo Gil answers:
Good afternoon,
Are you sure the pre_get_post is executing correctly?
If you use meta_query do not use "meta_key".
Also please separe the meta_query in a different variable to see it clearer:
$meta_query = array(
array(
'key' => 'event_start_date',
'value' => date('Y-m-d'),
'compare' => '>=',
'type' => 'DATE'
),
));
$query->set('meta_query',$meta_query);
User161833 comments:
Good afternoon.
Yes, I'm pretty sure it executes correctly. The problem I'm facing is that I only manage to sort the posts either by the first date (2017-03-06 in the example) or the last date (2017-03-14 in the example), but not by the earliest date that hasn't expired yet (2017-03-08 in the example) since 2017-03-06 has expired. I don't really have much skill with WP_Query to be able to solve this myself and I would need a solution rather quickly because of deadline.
Francisco Javier Carazo Gil comments:
Ah Ok I understand now.
You need some SQL added because of this.
Uhm let me think a moment.
Francisco Javier Carazo Gil comments:
Could you show me the SQL being generated?
Francisco Javier Carazo Gil comments:
Ok, another idea, you can first make a list of all ids of events in the future and use it:
$args = array(
'post_type' => $post_type,
'posts_per_page' => -1,
'meta_query' => array(
'relation' => 'AND',
array(
'key' => 'event_start_date',
'value' => date('Y-m-d'),
'compare' => '>=',
'type' => 'DATE'
)
)
);
$future_events = get_posts($args);
$future_events_ids = array();
foreach ($future_events as $p) :
$future_events_ids[] = $p;
endforeach;
$query->set('posts__in',$future_events_id);
$query->set( 'orderby', 'meta_value' );
$query->set( 'order', 'ASC' );
$query->set( 'meta_key', 'event_start_date' );
Sure we can improve the performance but it can be an option to explore.
User161833 comments:
I'll take this into consideration if no other solution can be found. It's quite a traffic heavy site so I need it to be as performant as possible.
Francisco Javier Carazo Gil comments:
Maybe you can improve it using some kind of cache: https://codex.wordpress.org/Function_Reference/wp_cache_set
You can improve the get_posts using this:
$args = array(
'post_type' => $post_type,
'posts_per_page' => -1,
'fields' => 'ids'
'meta_query' => array(
'relation' => 'AND',
array(
'key' => 'event_start_date',
'value' => date('Y-m-d'),
'compare' => '>=',
'type' => 'DATE'
)
)
);
$future_events = get_posts($args);
$future_events_ids = wp_list_pluck( $future_events, 'ID' );
User161833 comments:
This seems not to work, because let's say if another post has the start dates 2017-03-04 and 2017-04-01, the first query will include this post and then it gets ordered by the first date, 2017-03-04, which has already expired.
mod mi answers:
Hi, I guess this is in a loop of an archive for "events" post type? Since you have multiple event dates on each post you have to first query all the posts with future date events. Please notice that for the current_time you have to use the same format that you use to store the date in your custom fields 'event_start_date'.
$today = current_time('d M, y');
$args = array (
'post_type' => 'event',
'meta_query' => array(
array(
'key' => 'event_start_date',
'value' => $today,
'compare' => '>=',
),
),
'meta_key' => 'event_start_date',
'orderby' => 'meta_value',
'order' => 'ASC'
);
Luis Abarca answers:
Try without type
$query->set( 'orderby', 'meta_value' );
$query->set( 'order', 'ASC' );
$query->set( 'meta_query', array(
array(
'key' => 'event_start_date',
'value' => date('Y-m-d'),
'compare' => '>=',
),
));
User161833 comments:
I have tried that, but it yields the same result as this unfortunately:
$query->set( 'post_type', array('arrangement', 'bundle'));
$query->set( 'meta_query', array(
array(
'key' => 'event_start_date',
'value' => date('Y-m-d'),
'compare' => '>=',
'type' => 'DATE'
),
));
...and...
add_filter('posts_orderby', 'custom_events_orderby');
function custom_events_orderby($orderby_statement) {
global $wpdb;
if (!is_admin()) {
$orderby_statement = "MIN($wpdb->postmeta.meta_value) ASC";
}
return $orderby_statement;