Using 'or' within query parameters

Question

I have an entry which shows related events, based on the entry category, at the bottom. These events all have a start date and an end date - the end date is required, if the event is only one day, eventEndDate should be set to the same date as eventStartDate.

I have created a query with parameters to pull in related events, but only want events that are in the future. I need the query to take into account both start and end date - for example, the start date may have passed, but if the end date is still in the future this event can be shown. If both have passed, it should not show.

My difficulty is having the query treat these two parameters as 'OR' rather than 'AND'. I can't find docs on the correct syntax to achieve this. I have shown below the query as I currently have it. It establishes the current entry category; sets the parameters for section, checking for the start date being in the future and limits to 3 events, and then uses these parameters to pull in any related events that are not the current one.

{% set category = entry.eventCategory %}
{% set relatedParms = { section : 'schoolsAndEducators', eventStartDate: "> " ~ (now|atom), limit: '3' } %}
{% set relatedEvents = craft.entries(relatedParms).id('not ' ~ entry.id).orderBy('RAND()').all() %}

I'm not sure if it is possible, but I need to modify that 'relatedParams' query to also take eventEndDate into account. The below does not work, as it treats it like an 'AND' statement and only pulls in events for which both dates have passed:

{% set relatedParms = { section : 'schoolsAndEducators', eventStartDate: "> " ~ (now|atom), eventEndDate: "> " ~ (now|atom), limit: '3' } %}

I have checked similar questions here but either they aren't the same situation or I can't figure how to adjust the code for my particular requirements.

Answer 1

I had this exact same problem a while ago (calender view with a start date and an optional end date). I looked into how to combine multiple field filters with an OR-operator – as far as I can tell, it's not possible using criteria arrays or field-methods on the element query.

That leaves you with a custom where() condition. Formalized, the conditions for a current or future events are as follows:

1. The eventStartDate must not be empty. If the field is required in your setup, you don't have to check that in the query.
2. Either the eventStartDate OR the eventEndDate must be in the future.

Adding the other parameters to your query, you can use something like this:

{% set relatedEvents = craft.entries()
    .section('schoolsAndEducators')
    .id('not ' ~ entry.id)
    .eventStartDate(':notempty:')
    .andWhere([
        'or',
        ['>=', '[[field_eventStartDate_xxxxxxxx]]', now|atom],
        ['>=', '[[field_eventEndDate_xxxxxxxx]]', now|atom],
    ])
    .orderBy('RAND()')
    .limit(3)
    .all()
%}

A couple of notes regarding the field column names:

• The field_ prefix is used for all custom fields.
• All field columns get a suffix based on the fields UID since Craft 3.7. You need to replace the xxxxxxxx with the field suffix for your fields. You can use the helper method ElementHelper::fieldColumnFromField, see the discussions here and here for details. I've posted two options for using the field suffix in Twig in my answer here. Alternatively, you can just hardcode the field suffix in your template – it's based on the UID, so it won't change between environments.