Get entries filtered by date fields, one of which might be empty (and paginate them)

Question

I have an 'whatsOn' structure whose entries include two date fields: 'dateStart' and 'dateEnd'. The end date will be empty if the event is a one-dayer. I want to get all future events using the dateEnd (if not empty) or dateStart if not. So basically, all events that haven't ended yet. How do I add the 'dateEnd' bit to the following query:

{% set allEvents = craft.entries({
    section: 'whatsOn',
    limit: 20,
    level: 2,
    order: 'dateStart',
    dateStart: '>= ' ~ now
}) %}

Crucially, I'll be paginating them so I need this to return an ElementCriteriaModel, I can't just do the check inside a loop.

EDIT: I just noticed that date fields are just empty (not null/undefined) if you don't select or enter a date. Amended question to reflect this.

Answer 1

There's no way to simply add it to that query you have. But you can use the following workaround.

You first do all the logic within such conditionals you actually wanted to avoid, but instead of getting the ElementModels directly, you do an extra step and get / collect the entries' IDs using the ids() method.

When you're ready, use these IDs with the id property in a second ElementCriteriaModel. This you can paginate like you want!

Edit:
Here's some example code!

{% set cutoffDate = 'now'|date_modify('- ' ~ '7' ~ ' days') %}
{% set events = craft.entries.section('events').order('dateStart desc') %}

{% set eventIds = [] %}
{% for event in events if (event.dateEnd ? event.dateEnd : event.dateStart) >= cutoffDate %}
    {% set eventIds = eventIds|merge([event.id]) %}
{% endfor %}

{% set filteredEvents = craft.entries.id(eventIds).fixedOrder('true') %}