The query needs to grab from thousands of posts based on two custom fields. 'Original Date' is required for each post, with 'Featured Date' being optional (hidden based on a custom checkbox field. When checked, featured_date is set to original_date by default). However, if 'Featured Date' is set for a particular post, it should be priority and ultimately sorted by the featured date as if the original date was set to equal the featured date.
That is, the query does NOT need to sort all posts by featured date first, & then original date. Instead, it needs to sort them together as if those dates were combined into a single column. So a user could push the event to the top of the results by setting a featured date, but once another post is created with a later original date, the featured date post falls from the top.
Example database:
ID | post_title | meta_key | meta_value
---------------------------------------------
1 | Person 1 | original_date | Jan 2nd
2 | Person 2 | featured_date | Jan 1st
2 | Person 2 | original_date | Dec 1st
3 | Person 3 | featured_date | Jan 3rd
Expected query results:
ID | post_title | meta_key | meta_value
---------------------------------------------
3 | Person 3 | featured_date | Jan 3rd
1 | Person 1 | original_date | Jan 2nd
2 | Person 2 | featured_date | Jan 1st
And if a new post with an original (or featured) date of say Jan 4th is created, it would now be at the top of the results.
I've looked into meta_query, and can get the posts based on if those custom fields exist for a given post. But I haven't been able to find a way to order them such that the dates from both fields are combined and ordered, rather than ordering all by one field first then the other.
$args = array(
'post_type' => 'custom_post_type',
'post_status' => 'publish',
'paged' => $paged,
'meta_query' => array(
'relation' => 'OR',
array(
'key' => 'featured_date',
'type' => 'DATE',
'compare' => 'EXISTS'
),
array(
'key' => 'original_date',
'type' => 'DATE',
'compare' => 'EXISTS'
)
),
'orderby' => 'meta_value_date',
'order' => 'DESC',
// 'orderby' => array('meta_value_date' => 'DESC')
);
$results = new WP_Query( $args );
I am able to get the results I want from the below SQL query (albeit likely inefficient as I don't know much about SQL). However, the issue with using this SQL query is there is already logic that adds additional arguments to the initial query based on other factors. I would have to re-implement adding to the SQL query based on that logic, which I'd like to avoid by figuring out the proper wp_query way of doing this.
$sql = $wpdb->get_results("SELECT * FROM (
SELECT DISTINCT *, $wpdb->postmeta.meta_value AS sort_date
FROM $wpdb->posts
INNER JOIN $wpdb->postmeta ON ( $wpdb->posts.ID = $wpdb->postmeta.post_id )
WHERE $wpdb->posts.post_type = 'custom_post_type'
AND $wpdb->postmeta.meta_key = 'featured_date'
AND $wpdb->posts.post_status = 'publish'
UNION
SELECT DISTINCT *, $wpdb->postmeta.meta_value AS sort_date
FROM $wpdb->posts
INNER JOIN $wpdb->postmeta ON ( $wpdb->posts.ID = $wpdb->postmeta.post_id )
WHERE $wpdb->posts.post_type = 'custom_post_type'
AND $wpdb->postmeta.meta_key = 'original_date'
AND $wpdb->posts.post_status = 'publish'
) AS created_table
GROUP BY ID
ORDER BY sort_date DESC");
Edit - birgire's response made me realize it's worth mentioning this is for a plugin that is already being used in production on numerous sites. The current functionality orders the posts by 'original_date'. But it's been requested to allow the user to push certain posts to the top, while still respecting the queue (ie, later posts will be above it). The featured_date is the requested functionality.
The query needs to grab from thousands of posts based on two custom fields. 'Original Date' is required for each post, with 'Featured Date' being optional (hidden based on a custom checkbox field. When checked, featured_date is set to original_date by default). However, if 'Featured Date' is set for a particular post, it should be priority and ultimately sorted by the featured date as if the original date was set to equal the featured date.
That is, the query does NOT need to sort all posts by featured date first, & then original date. Instead, it needs to sort them together as if those dates were combined into a single column. So a user could push the event to the top of the results by setting a featured date, but once another post is created with a later original date, the featured date post falls from the top.
Example database:
ID | post_title | meta_key | meta_value
---------------------------------------------
1 | Person 1 | original_date | Jan 2nd
2 | Person 2 | featured_date | Jan 1st
2 | Person 2 | original_date | Dec 1st
3 | Person 3 | featured_date | Jan 3rd
Expected query results:
ID | post_title | meta_key | meta_value
---------------------------------------------
3 | Person 3 | featured_date | Jan 3rd
1 | Person 1 | original_date | Jan 2nd
2 | Person 2 | featured_date | Jan 1st
And if a new post with an original (or featured) date of say Jan 4th is created, it would now be at the top of the results.
I've looked into meta_query, and can get the posts based on if those custom fields exist for a given post. But I haven't been able to find a way to order them such that the dates from both fields are combined and ordered, rather than ordering all by one field first then the other.
$args = array(
'post_type' => 'custom_post_type',
'post_status' => 'publish',
'paged' => $paged,
'meta_query' => array(
'relation' => 'OR',
array(
'key' => 'featured_date',
'type' => 'DATE',
'compare' => 'EXISTS'
),
array(
'key' => 'original_date',
'type' => 'DATE',
'compare' => 'EXISTS'
)
),
'orderby' => 'meta_value_date',
'order' => 'DESC',
// 'orderby' => array('meta_value_date' => 'DESC')
);
$results = new WP_Query( $args );
I am able to get the results I want from the below SQL query (albeit likely inefficient as I don't know much about SQL). However, the issue with using this SQL query is there is already logic that adds additional arguments to the initial query based on other factors. I would have to re-implement adding to the SQL query based on that logic, which I'd like to avoid by figuring out the proper wp_query way of doing this.
$sql = $wpdb->get_results("SELECT * FROM (
SELECT DISTINCT *, $wpdb->postmeta.meta_value AS sort_date
FROM $wpdb->posts
INNER JOIN $wpdb->postmeta ON ( $wpdb->posts.ID = $wpdb->postmeta.post_id )
WHERE $wpdb->posts.post_type = 'custom_post_type'
AND $wpdb->postmeta.meta_key = 'featured_date'
AND $wpdb->posts.post_status = 'publish'
UNION
SELECT DISTINCT *, $wpdb->postmeta.meta_value AS sort_date
FROM $wpdb->posts
INNER JOIN $wpdb->postmeta ON ( $wpdb->posts.ID = $wpdb->postmeta.post_id )
WHERE $wpdb->posts.post_type = 'custom_post_type'
AND $wpdb->postmeta.meta_key = 'original_date'
AND $wpdb->posts.post_status = 'publish'
) AS created_table
GROUP BY ID
ORDER BY sort_date DESC");
Edit - birgire's response made me realize it's worth mentioning this is for a plugin that is already being used in production on numerous sites. The current functionality orders the posts by 'original_date'. But it's been requested to allow the user to push certain posts to the top, while still respecting the queue (ie, later posts will be above it). The featured_date is the requested functionality.
On post insert/update, set the post's menu_order as the maximum of the numeric MMDD values of original_date and featured_date. Maybe set the menu order in the format 1MMDD to avoid a leading zero.
Then we can order the custom type posts by the menu order very fast with:
WP_Query( [
'post_type' => 'custom_post_type',
'orderby' => 'menu_order',
'order' => 'DESC'
] );
where menu_order is a int(11) column in the posts table.
If not all of the custom type posts have these custom dates, group them e.g. by a custom taxonomy, to use in your WP_Query. Maybe one could instead look for posts with non-zero menu order.