WP_QUERY order posts by two combined meta_value dates

Question

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");
0
user194575 4 months 0 Answers 23 views 0

Leave an answer