Dylan Fisher Dylan Fisher - 1 month ago 19
MySQL Question

Complex meta_query and orderby with multiple custom post types, and multiple date column names

I am trying to query two custom post types,

exhibitions
and
events
.

Exhibitions have
exhibition_start_date
and
exhibition_end_date
custom fields.

Events have a
event_date
custom field.

How would I go about sorting multiple columns in one orderby clause?

I've been looking at this for reference https://make.wordpress.org/core/2015/03/30/query-improvements-in-wp-4-2-orderby-and-meta_query/.

Ideally my results would look like:


  • Exhibition (Oct 2016)

  • Event (Sept 2016)

  • Event (Aug 2016)

  • Exhibition (July 2016)

  • Event (June 2016)



My query so far, which is not sorting properly:

$today = date('Ymd');
$past_date = date( 'Ymd', strtotime( $today . ' - 12 months' ) );

$args = array(
'post_type' => array( 'exhibition', 'event' ),
'meta_query' => array(
'relation' => 'OR',
'exhibition_clause' => array(
array (
'key' => 'exhibition_end_date',
'compare' => '<',
'value' => $today,
),
array (
'key' => 'exhibition_end_date',
'compare' => '>=',
'value' => $past_date,
)
),
'event_clause' => array(
array (
'key' => 'event_date',
'compare' => '<',
'value' => $today,
),
array (
'key' => 'event_date',
'compare' => '>=',
'value' => $past_date,
)
)
),
'orderby' => array(
'exhibition_clause' => 'DESC',
'event_clause' => 'DESC',
),
);


EDIT: after hooking into the 'posts_orderby' filter:

The filter:

function my_exhibition_event_date_posts_orderby( $orderby ) {
if ( is_exhibition_event_query() ) {
unset( $GLOBALS['is_exhibition_event_query'] );
return "IFNULL(mt1.meta_value+0, mt2.meta_value+0) DESC";
}
return $orderby;
}
add_filter( 'posts_orderby', 'my_exhibition_event_date_posts_orderby', 10, 1 );


The sql output:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) INNER JOIN wp_postmeta AS mt2 ON ( wp_posts.ID = mt2.post_id ) INNER JOIN wp_postmeta AS mt3 ON ( wp_posts.ID = mt3.post_id ) WHERE 1=1 AND (
(
( wp_postmeta.meta_key = 'exhibition_end_date' AND wp_postmeta.meta_value < '20161023' )
AND
( mt1.meta_key = 'exhibition_end_date' AND mt1.meta_value >= '20080623' )
)
OR
(
( mt2.meta_key = 'event_date' AND mt2.meta_value < '20161023' )
AND
( mt3.meta_key = 'event_date' AND mt3.meta_value >= '20080623' )
)
) AND wp_posts.post_type IN ('exhibition', 'event') AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled' OR wp_posts.post_author = 1 AND wp_posts.post_status = 'private') GROUP BY wp_posts.ID ORDER BY IFNULL(mt2.meta_value+0, mt1.meta_value+0) DESC LIMIT 0, 20


This still isn't right, though. This is the order the posts are returned in:


  • Event (Oct 15, 2014)

  • Event (Oct 16, 2014)

  • Event (Oct 31, 2014)

  • Event (Aug 6, 2014)

  • Exhibition (Oct 14, 2016)

  • Exhibition (Oct 22, 2014)



When they should be ordered by end date, with the most recent dates first.

Answer

Unfortunately I wasn't able to figure out the proper query to get the order I wanted straight from the database. However, I did come up with an easy usort function to compare meta values.

To further complicate the SQL needed to order this, the exhibition_start_date is stored in the format Ymd, while the event_date is stored in format Y-m-d H:i:s.

usort( $the_query->posts, 'my_compare_exhibition_and_event_dates_descending' );

function my_compare_exhibition_and_event_dates_descending($a, $b) {
  $a_date = get_post_meta( $a->ID, 'exhibition_start_date', true );
  if ( empty( $a_date ) ) {
    $a_date = get_post_meta( $a->ID, 'event_date', true );
    $a_date = DateTime::createFromFormat( 'Y-m-d H:i:s', $a_date )->format( 'Ymd' );
  }

  $b_date = get_post_meta( $b->ID, 'exhibition_start_date', true );
  if ( empty( $b_date ) ) {
    $b_date = get_post_meta( $b->ID, 'event_date', true );
    $b_date = DateTime::createFromFormat( 'Y-m-d H:i:s', $b_date )->format( 'Ymd' );
  }

  return ( $a_date > $b_date ) ? -1 : 1;
}