Intro

Recently I was asked to build a WordPress plugin that would allow users to create and display events. Basically, it was a matter of creating a custom post type and a few metaboxes. But, as I worked on it. I happened to stumble across some things of interest around how best to handle post metadata and the WordPress query. So, I thought I'd take some time to write it up and share what I found.

I'd also like to thank my friends at WPCampus who helped me work through some of these initial ideas. As always, they were very patient while I thought out loud!

Prerequisite

I'm not going to go over how to: create a WordPress plugin, create a custom post type or make post metaboxes. This post assumes you know how to do those things.

Project scope

There were a few requirements for this project. The relevant ones are:

  • Events should be ordered in ascending chronological order (earliest events at the top of the list)
    • Events can be either one or many days
    • Events in the past should not be displayed unless...
    • The end date for the event is in the future
  • Events should be searchable by

How I inititally set myself up to fail

In my mind, I was focused on start dates for the events. After all, events have to occur on a particular date. Otherwise, they're not events. But they don't necessarily need an end date. In a world where events are only one day long, maybe you can safely ignore the end date. So I created a data scenario where I would store post meta data like this

| meta_id | post_id | meta_key | meta_value | | ---------- | ---------- | ---------- | ---------- | | 1 | 1 | event_date | 2021-10-01 | | 2 | 1 | event_end_date | | | 3 | 2 | event_date | 2021-10-01 | | 4 | 2 | event_end_date | 2021-10-05 |

Note that post 1 has an empty end date.

Now, I'm trying to be at least a little smart about this. I know that the best time to make this kind of query is in the WordPress pre_get_posts filter. This will save me the trouble of creating and throwing away a new query in my page template.

<?php
$args = [];
$letsAvoidThisQuery = new WP_Query($args);
// do some stuff
wp_reset_query();

Yeah... no.

Not if I can avoid it.

The better way is to do this...

add_filter('pre_get_posts', 'event_post_filter');

function event_post_filter($query) {
  if (is_admin()) {
    return $query;
  }

  if ($query->is_archive() && 'event-post' === $query->query['post_type']) {
    $toSet = [];

    foreach ($toSet as $key => $value) {
      $query->set($key, $value);
    }
  }
  return $query;
}

Doing things this way, I can build up my own query before it ever gets to the template. Much faster.

But the question is - What should go in the $toSet array? I know that it needs to be a meta_query because the data I need is in the wp_postmeta table. I'm trying to follow my business requirements, so I come up with an idea to have a start_clause and end_clause, and then order those results.

<?php

$toSet = [
  'meta_query' => [
    'relation' => 'OR',
    'start_clause' => [
      'key' => 'event_date',
      'value' => current_time('Y-m-d'),
      'compare' => '>=',
      'type' => 'DATE'
    ],
    'end_clause' => [
      'relation' => 'AND',
      [
        'key' => 'event_date',
        'compare' => 'EXISTS'
      ],
      [
        'key' => 'event_end_date',
        'value' => current_time('Y-m-d'),
        'compare' => '>=',
        'type' => 'DATE'
      ]
    ]
  ],
  'orderby' => [
    'start_clause',
    'end_clause'
  ]
];

Well this (and a bunch of permutations) went REALLY BADLY. It went so badly, that I ended up throwing the pre_get_posts idea away and going down a rabbit hole writing my own raw SQL queries to handle the initial request using the posts_request filter. This filter allows you to change the request string anyway you want. Which is fine up until a point. As soon as I needed to implement my search features, it became so fragile so quickly that it was obvious I needed to throw the whole thing away.

So I kick myself for not recognizing the obvious problem

Let this be a lesson to all of us. The better you structure your data, the easier it is to work with. My problem was in the original conception of what the data needed to look like. I should never have said "all events have a start date but only some have an end date". After all, even if an event is only one day long, it still ends on a date! The same date on which it started, but that makes all the difference. Now, my metatable can look like this

| meta_id | post_id | meta_key | meta_value | | ---------- | ---------- | ---------- | ---------- | | 1 | 1 | event_date | 2021-10-01 | | 2 | 1 | event_end_date | 2021-10-01 | | 3 | 1 | event_duration | single | | 4 | 2 | event_date | 2021-10-01 | | 5 | 2 | event_end_date | 2021-10-05 | | 6 | 2 | event_duration | multiple |

With data structured like this, I can query my data with the following ideas

  • Get any post where the end date is not in the past (or is still in the future if you prefer a positive spin)
  • Order the posts by the start date

This is a much simpler set of rules. Now I just need to implement them in the query.

An aside

The event_duration meta data comes from a custom metabox that's just two radio buttons. Selecting the "single" button (the default) forces the start and end dates to lock together. It also sets the end date input to readonly. Switching to the "multiple" button releases that lock. Dates are constrained so that you can't have an end date before the start date or vice versa.

The orderby clause

The orderby property of the query is interesting. There are a shocking number of ways you can order things. The one that's most relevant right now is that you can order by meta_value but only if you have a meta_key to associate it with.

meta_value – Note that a ‘meta_key=keyname‘ must also be present in the query.

Back to the query

Now, I still need to have both a start and end clause for the query. Why? Because of the rules above

  • End dates must not be in the past
  • Posts must be ordered by their start dates
  • Somewhere there has to be a meta_key to order by
<?php

$toSet = [
  'meta_query' => [
    'relation' => 'AND',
    'start_clause' => [
      'key' => 'event_date',
      'compare' => 'EXISTS',
    ],
    'end_clause' => [
      'key' => 'event_end_date',
      'value' => current_time('Y-m-d'),
      'compare' => '>=',
      'type' => 'DATE'
    ]
  ],
  'orderby' => [
    'start_clause',
  ]
];

That set of parameters yields this in the final function.

add_filter('pre_get_posts', 'event_post_filter');

function event_post_filter($query) {
  if (is_admin()) {
    return $query;
  }

  if ($query->is_archive() && 'event-post' === $query->query['post_type']) {
    $toSet = [
      'meta_query' => [
        'relation' => 'AND',
        'start_clause' => [
          'key' => 'event_date',
          'compare' => 'EXISTS',
        ],
        'end_clause' => [
          'key' => 'event_end_date',
          'value' => current_time('Y-m-d'),
          'compare' => '>=',
          'type' => 'DATE'
        ]
      ],
      'orderby' => [
        'start_clause',
      ]
    ];

    foreach ($toSet as $key => $value) {
      $query->set($key, $value);
    }
  }
  return $query;
}

This way I can now also perform searches by directly modifying the query rather than try to wrangle raw SQL.

Conclusion

I think the biggest takeaway here is that rather than wrestle with what I thought was "wrong" with WordPress, I needed a clearer idea of what the data should be before I started. We can debate the WordPress's quirks, but I don't think we can debate the efficacy of well structured data. The other takeaway is that sometimes, it's worth throwing things away and starting over. It can be useful to explore an idea. You can learn a lot that way. But there's a big difference between exploring a potential idea and getting attached to it. When you're attached to an idea, you can miss many (and sometimes) more useful ideas.