3

I'm trying to write a meta query that looks for either events starting within the next year (event_start_date between now and 52 weeks), or events that are still going on (event_end_date between now and 52 weeks), and then sorts them together via the event_start_date into one query to make a chronological event list.

My code successfully queries both and returns the results, but doesn't sort them together. They display in the order below:

12th Sept
13th Sept
18th Sept
11th - 13th Sept (end date)
20th Sept
27th Sept
25th Sept
26th Sept
20th Sept
19th Sept

Here is my code:

$date_1 = date('Ymd', strtotime("now"));
$date_2 = date('Ymd', strtotime("52 weeks"));

$args = array(
    'post_type' => 'event',
    'order' => 'ASC',
    'meta_key' => 'event_start_date',
    'posts_per_page' => 10,
    'meta_query' => array(
        'relation' => 'OR',
        array(
            'key' => 'event_start_date',
            'value' => array( $date_1, $date_2 ),
            'type' => 'numeric',
            'compare' => 'BETWEEN'
        ),
        array(
            'key' => 'event_end_date',
            'value' => array( $date_1, $date_2 ),
            'type' => 'numeric',
            'compare' => 'BETWEEN'
        ),
    ),
    'orderby' => 'meta_value_num'
);

$wp_query = new WP_Query( $args );

while( $wp_query->have_posts() ) {
    $wp_query->the_post();
}
  • How are your metavalues saved? Did you try setting the type to DATE? – fischi Sep 12 '14 at 9:40
  • @fischi yeah they're set as DATE, in the format YYYYMMDD – Jacktionman Sep 12 '14 at 23:12
1

WordPress standard query sucks with multiple meta queries when orderby is set to meta key.

After a lot of headaches and time lost I wrote a custom class that allow me to better handle that sort of things. Here there is a simplified version of my class, but it hould work for your purposes:

class EventQuery {

  private $args;
  private $order;
  private $query;

  static function query( Array $args, \WP_Query $query = NULL ) {
    if ( is_null( $query ) ) {
      $query = new \WP_Query;
    }
    $class = __CLASS__;
    $eventquery = new $class( $args, $query );
    return $eventquery->enable()->run()->disable()->getQuery();
  }

  function __construct( Array $args, \WP_Query $query ) {
    $this->args = $args;
    $this->order = isset( $args['order'] ) && strtoupper( $args['order'] === 'DESC' )
      ? 'DESC'
      : 'ASC';
    $this->query = $query;
  }

  function enable() {
    add_filter( 'posts_fields_request',  array( $this, 'filterFiels' ) );
    add_filter( 'posts_join_request',    array( $this, 'filterJoin'  ) );
    add_filter( 'posts_where_request',   array( $this, 'filterWhere' ) );
    add_filter( 'posts_orderby_request', array( $this, 'filterOrder' ) );
    return $this;
  }

  function disable() {
    remove_filter( 'posts_fields_request',  array( $this, 'filterFiels' ) );
    remove_filter( 'posts_join_request',    array( $this, 'filterJoin'  ) );
    remove_filter( 'posts_where_request',   array( $this, 'filterWhere' ) );
    remove_filter( 'posts_orderby_request', array( $this, 'filterOrder' ) );
    return $this;
  }


  function filterFiels( $sql ) {
    return $sql . ", metastartdate.meta_value as start_date";
  }

  function filterJoin( $sql ) {
    global $wpdb;
    return $sql . " INNER JOIN {$wpdb->postmeta} 
        AS metastartdate ON ({$wpdb->posts}.ID = metastartdate.post_id)";
  }

  function filterWhere( $sql ) {
    return $sql . " AND metastartdate.meta_key = 'event_start_date'";
  }

  function filterOrder( $sql ) {
    return "start_date {$this->order}";
  }

  function run() {
    $this->query->query( $this->args );
    return $this;
  }

  function getQuery() {
    return $this->query;
  }

}

It can be used like so:

$args = array (
  'post_type' => 'post',
  'posts_per_page' => 10,
  'meta_query' => array(
    'relation' => 'OR',
    array(
      'key' => 'event_start_date',
      'value' => array( $date_1, $date_2 ),
      'type' => 'numeric',
      'compare' => 'BETWEEN'
    ),
    array(
      'key' => 'event_end_date',
      'value' => array( $date_1, $date_2 ),
      'type' => 'numeric',
      'compare' => 'BETWEEN'
    ),
  ),
  'orderby' => 'custom', // <-- NOTE THIS 
  'order' => 'ASC'   // <-- AND THIS 
);

$query = EventQuery::query( $args ); // return a WP_Query

if ( $query->have_posts( ) ) {
  // loop here
}

Of course you can use 'DESC' to order by desc start date.

  • That's wonderful! It worked a treat. I hadn't tried adding a class to Wordpress before - great solution. Thanks! – Jacktionman Sep 12 '14 at 23:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.