2

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.

3
  • 1
    Any reason why you can't just drop the startDate in the query? Unless I've read your question incorrectly, If the start + end dates are the same in some instances, you're really going to be querying just the endDate anyway. Also since you're really only looking for dates that aren't "expired" yet, the start date doesn't really matter. The only time you'd want to query the start date would be if you wanted to look for events in a small sliver of time (eg "beginning next week but before next month) but that doesn't seem to be the case here? Nov 21 '21 at 23:43
  • 1
    @RitterKnight ... You make a most interesting point my friend lol. I think you're completely right. To be perfectly honest, I think I didn't realise this because it started out that only the start date was required and then changed because it was causing more issues. So I had to check for both because they may not always have entered an end date. But now that they do, yes you're correct and I really only need to check the end date. After all this!! Clearly just getting my brain tied up in knots over this project!
    – Grace
    Nov 22 '21 at 15:23
  • 1
    I've been there! Sometimes the best solution is the simplest. (But sometimes not...) Nov 22 '21 at 23:44
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.
2
  • 1
    Well that's just marvellous, worked like an absolute charm. I hardcoded the suffix because while it does feel weird to hardcode it, if it doesn't change then why give myself the extra bother lol. Thanks for coming to the rescue yet again! Can you tell I don't know what I'm doing lol
    – Grace
    Nov 19 '21 at 15:08
  • 1
    Yeah I ended up hardcoding the suffix in my template as well … ^^ Nobody has it all figured out, the important thing is to ask the right questions and stay curious!
    – MoritzLost
    Nov 19 '21 at 15:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.