WP_Query meta_query results date by date

by

Don’t leave! This isn’t another question about how to orderby meta_value!

I have a post type called events, and each post I create has a meta field I imaginatively called event_dates_wp which accepts multiple dates and saves them as an array in Ymd format.

I have a WP_Query which returns posts with a date between the two meta_query values, like so:

$args = [
    'post_type' => 'event',
    'order' => 'ASC',
    'orderby' => 'meta_value',
    'meta_key' => 'event_dates_wp',
    'posts_per_page' => -1,
    'meta_query' => [
      [
        'key' => 'event_dates_wp',
        'type' => 'DATE',
        'value' => '20180801',
        'compare' => '>='
      ],
      [
        'key' => 'event_dates_wp',
        'type' => 'DATE',
        'value' => '20180831',
        'compare' => '<='
      ]
    ]
  ];

This all works wonderfully, and returns a list of posts which contain a event_dates_wp value which falls between 1st of August 2018 and 31st of August 2018; but the results are ‘post centric’ rather than ‘date centric’ – let me explain myself:

The above is what one would expect, but what I want to do is list the dates, starting with the 1st of August, all the way through to the 31st, with each matching post listed under each date, allowing them to repeat.

For example:

Event A contains dates [20180801, 20180802, 20180803, 20180804]
Event B contains dates [20180803, 20180804, 20180805]
Event C contains dates [20180804, 20180807]

The results would look something like:

20180801 // 1st Aug 2018
Event A

20180802 // 2nd Aug 2018
Event A

20180803 // 3rd Aug 2018
Event A
Event B

20180804 // 4th Aug 2018
Event A
Event B
Event C

20180805 // 5th Aug 2018
Event B

20180806 // 6th Aug 2018
[NO RESULT]

20180807 // 7th Aug 2018
Event C

20180808 // 8th Aug 2018
[NO RESULT]

20180809 // 9th Aug 2018
[NO RESULT]
...

The only way I can think to do this would be to query a single date at a time, then moving on to query the next; which can’t be a good idea. Is there a way to achieve this seemingly common pattern of querying date based events and formatting them as described?

A good example of this kind of behaviour is the Barbican events page, which I believe is using Drupal. There surely must be a WordPress way to do this kind of thing?

Thanks for reading!

1 Answer
1

My idea is following.

1. Get the array of all meta dates your events have. DISTINCT in the MySQL query means do not include duplicates.

<?php
/*
 * From https://wordpress.stackexchange.com/a/9451/11761
 */
function get_all_possible_meta_dates( $key = 'event_dates_wp', $type="page", $status="publish" )
{

    global $wpdb;

    $result = $wpdb->get_col( $wpdb->prepare( "
        SELECT DISTINCT pm.meta_value FROM {$wpdb->postmeta} pm
        LEFT JOIN {$wpdb->posts} p ON p.ID = pm.post_id
        WHERE pm.meta_key = '%s'
        AND p.post_status="%s"
        AND p.post_type="%s"
    ", $key, $status, $type ) );

    return $result;
}

2. Loop through all posts having event_dates_wp meta key and get the posts object. You need it once.

3. Compare each post meta to the possible dates you have. If there is match, print it out.

<?php
function print_posts_by_meta_dates()
{

    $args = [
        'post_type' => 'event',
        'order' => 'ASC',
        'orderby' => 'meta_value',
        'meta_key' => 'event_dates_wp',
        'posts_per_page' => -1,
    ];

    // all events having `event_dates_wp`
    $loop = new WP_Query( $args );

    // all posssible event dates
    $distinct_meta_dates = get_all_possible_meta_dates();

    foreach( $distinct_meta_dates as $event_meta_date ) {

        // date header
        echo '<h1>' . $event_meta_date . '</h1>';

        // loop through all events
        foreach( $loop->posts as $event ) {

            // get current event dates array
            $event_all_dates = get_post_meta( $event->ID, 'event_dates_wp', false );

            // compare
            if( in_array( $event_meta_date, $event_all_dates ) ) {

                // post name
                echo '<p>' . $event->post_name;

            }
        }
    }
}

4. Print the post list using print_posts_by_meta_dates() function in a template.

5. Or create the shortcode to use inside the post or page.

add_shortcode( 'test_shortcode', 'print_posts_by_meta_dates' );

You’ll get something similar to:

  • 20180801
    • test-page-1
  • 20180802
    • test-page-1
    • test-page-2
    • test-page-3
  • 20180803
    • test-page-2
    • test-page-3

Remember, do not separate custom fields with e. g. comma, but add them separately:

enter image description here

Leave a Comment