0

This loop containing a query is (surprise surprise) proving very intensive.

There is also a second very similar query on the page to get all of the events related to this year and list them by just the start date.

Is there a way to optimise them, perhaps by storing/caching a general query for all entries in the 'events' section which match 'year' - and then searching within that query to group the entries by date?

I've got around this until now by caching the output of the entire templatebut this feels naughty.

{# List events, grouped by day #}
{% for dateRow in entry.festivalDates %}

    {# Set display state depending on if the date is past/future #}
    {% set dateDisplayState = (dateRow.date|date_modify("+1 day") >= now ) ? 'true' : 'false' %}

    <tbody class="list" x-data="{ open: {{ dateDisplayState }} }">
        {% set dateFormatted = dateRow.date|date('d-m-Y') %}
        {# Show/hide toggle with AlpineJS #}
        <tr class="cursor-pointer" @click="open = !open"><th><h3 id="day{{ loop.index }}" class="mt-12 mb-2 text-2xl font-normal text-left md:text-4xl">{{ dateFormatted|date('l d F') }} <span class="border-black uppercase text-xs" x-text="open ? 'hide' : 'show'"></span></h3></th></tr>
        {% set eventsThisDay = craft.entries()
            .section('events')
            .relatedTo(year)
            .search("eventDates:#{dateFormatted}")
            .with('heroImage','eventTypepicker','eventRegion')
            .all()
        %}
        {% for event in eventsThisDay %}
            {% include('_partials/eventRow') with { showRegion: true, showDate: false, showCategory: true } %}
        {% endfor %}
    </tbody>
{% endfor %}

And the other query (inside a Sprig component):

{% set events = craft.entries()
    .section('events')
    .with([['heroImage', { kind: 'image'}]],['eventTypepicker'],['eventRegion'])
    .offset(offset)
    .orderBy('startDate asc')
    .limit(limit)
    .relatedTo(year)
    .all()
%}

Here's an overview of the frontend view and the entries/fields - festivalDates is a Matrix field on the Festival entry with a date in each block, and eventDates is a free text field on the Event entry with a comma-separated list of dates for that event.

Screenshot of screens

1
  • Thinking about this some more, maybe I could do the thing where you first do a query to get the IDs of all the events, then work with these directly for both queries [unsure of specifics though] Jan 20, 2023 at 8:25

1 Answer 1

1

You can definitely make this work with a single query. Once you have all the events in the given year, you can filter or group that list without using additional queries. Start with a query to find all events in the current year (I'll leave out the eager-loading for brevity):

{% set eventsThisYear = craft.entries()
    .section('events')
    .relatedTo(year)
    .all()
%}

Now if every event took place only on a single date, you could use the group filter to group events by day and then loop through that. However, it looks like your events can take place on multiple days and therefore need to appear in more than one place. So instead, you can loop through all event days (like you're already doing) and repeatedly search the list of all events using the filter filter, reducing the list to those events that take place on the current day.

How you filter the list depends on how the event dates are stored – based on your screenshot, it looks like a comma-separated list of dates in d-m-Y format? In this case, you can filter the list by checking if the eventDates fields includes the current date in the loop in this format.

{% for dateRow in entry.festivalDates %}
    {% set dateFormatted = dateRow.date|date('d-m-Y') %}
    {% set eventsThisDay = eventsThisYear|filter(
        event => dateFormatted in event.eventDates
    ) %}
{% endfor %}

The containment operator (in) checks for substrings.

I would recommend to maybe store the event dates in a more robust way, like the Super Table field you're using for the Festival Dates field. A comma-separated list is inconvenient to search or sort by. If you insist on the comma-separated list, at least use YYYY-MM-DD format for sanity (see ISO 8601). You can still format the dates differently in the output.

Regarding your second query, once you have the full list, you can also sort it by a specific value using the sort filter. Assuming the start date is the first date in the comma-separated list, use the sort filter and the split filter to get the first date, then use that for the comparison to sort by date. This will be much easier if you use YYYY-MM-DD format to store the dates, since this format sorts naturally in chronological order.

5
  • Amazing, thank you! About to test this out. Jan 20, 2023 at 9:56
  • PS. The reason for storing the dates in a comma-separated list is because I'm using this jQuery plugin on a custom field (to create the calendar picker in the admin panel) - dubrox.github.io/Multiple-Dates-Picker-for-jQuery-UI Jan 20, 2023 at 9:57
  • @JamesGreig You could still store them differently server-side – though I don't know all the requirements, and of course it's also a question of budget. In any case, I would still switch the date format to YYYY-MM-DD, looks like the jQuery plugin supports this: dubrox.github.io/Multiple-Dates-Picker-for-jQuery-UI/…
    – MoritzLost
    Jan 20, 2023 at 10:22
  • @JamesGreig Not really related to your question , but the festival dates list seems a bit redundant given that you're already storing the dates of the events..? Could you instead use an entry select field against each festival to choose its related events, then infer the festival dates from the dates of all of its events? Jan 20, 2023 at 13:12
  • I agree @JamesSmith but also figured it was quicker and more accurate to do manually than try and figure that out : ) Jan 20, 2023 at 16:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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