Complex date range with WP_Query and BETWEEN

Question

Backstory

We have a bunch of events inside an events custom post type. They each have the following custom fields associated to them:

• start_date
• end_date

On the front-end, we have a sidebar with filters where the user can choose how they want to search events (ie., various taxonomies, start date for their trip in town, end date, etc). The event_begin and event_end search inputs are what we'll use to query the database off of.

The problem

I'm having issues including a loosely queried result. Strict matches show up, but the stakeholder for the project is saying that the search is too strict. I've brought up the concept of abandoning the front-end event_end field in the user's search filters, but I'm not entirely sure that's necessary at this point myself.

If I search for events, I can structure my meta_query where:

• events with a start date and end date between the two chosen dates will show up
• events where the start date occurs between the two chosen dates will show up
• events where the end date occurs between the two chosen dates will show up

But what about when I front-end search like this:

• event_begin 7/15/2016
• event_end 7/15/2016

...but have an event in the database where its custom fields show the event to actually be one week long 7/11/2016 - 7/16/2016.

That event wouldn't return from my query, because none of the above matches. But technically if I'm in town on 7/15/2016 (today), I should be able to see (and possibly attend) that event while in town on business for the day.

Thoughts

Generally, we don't store an event range as separate dates for each day in the series. We (as humans) store date ranges; so I'm sure there's some logical way to approach this. Just not coming up with anything in my searching or research.

Thank you for your help and thoughts!

Answer 1

It's a little hard to think in meta_query code for complicated case like this and without access to data. :)

What you have working can be expressed as two following conditions:

• start_date BETWEEN event_begin and event_end
• end_date BETWEEN event_begin and event_end

Your first case with both in between seems irrelevant, since these two will catch it anyway.

The challenge you are having with the rest of it is that you want reverse logic — event_begin or event_end BETWEEN start_date and end_date. But you cannot express it in same way since those are unique to each event.

But you don't really care when they are, just that they around search dates.

So two more conditions can be expressed as:

• ( start_date <= event_begin ) AND ( end_date >= event_begin ) (event dates at or around search start)
• ( start_date <= event_end ) AND ( end_date >= event_end ) (event dates at or around search end)

Again, I struggle to write this as meta query "in theory", but since meta queries can be nested you should be able to stuff this all into their logic.

PS Performance might become a consideration and call for aggregate of multiple simpler queries rather than single complex one.