I have a custom post type for events which has a date/time field in a repeater to hold the event dates. That could be a single date, several consecutive dates, or even a repeating event with one date a week for several months. It has been easy to query for future events with the information found here, but I also need to sort the events in descending order starting from the current date. Using orderby on that custom field almost works, but it breaks in one key area. It will determine the order based on all the occurrences of the repeater field, not just the ones in the future.
For example, say it’s Friday. Event A recurs every Thursday, so it happened yesterday and will happen next Thursday. Ideally, Event B that happens tomorrow (Saturday) should sort ahead of Event A, but because of Event A’s occurrence yesterday, it sorts ahead of Event B. So, somehow, I need to remove all past event dates from the repeater field prior to sorting.
I thought of creating a second custom field that contains the next occurrence of each event and populating it daily with a cron job, but that seems like a bit of a hack and I hate using cron (much less the fairly unreliable WordPress cron) for anything like that. Any other suggestions? Perhaps some way to use a custom ORDERBY in the sql clause that eliminates past dates from the repeater field?