<p>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):</p>
<pre class="lang-php prettyprint-override"><code>{% set eventsThisYear = craft.entries()
    .section('events')
    .relatedTo(year)
    .all()
%}
</code></pre>
<p>Now if every event took place only on a single date, you could use the <a href="https://craftcms.com/docs/4.x/dev/filters.html#group" rel="nofollow noreferrer"><code>group</code> filter</a> 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 <a href="https://twig.symfony.com/doc/3.x/filters/filter.html" rel="nofollow noreferrer"><code>filter</code> filter</a>, reducing the list to those events that take place on the current day.</p>
<p>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 <code>d-m-Y</code> format? In this case, you can filter the list by checking if the <code>eventDates</code> fields includes the current date in the loop in this format.</p>
<pre class="lang-twig prettyprint-override"><code>{% for dateRow in entry.festivalDates %}
    {% set dateFormatted = dateRow.date|date('d-m-Y') %}
    {% set eventsThisDay = eventsThisYear|filter(
        event =&gt; dateFormatted in event.eventDates
    ) %}
{% endfor %}
</code></pre>
<p>The <a href="https://twig.symfony.com/doc/3.x/templates.html#containment-operator" rel="nofollow noreferrer">containment operator (<code>in</code>)</a> checks for substrings.</p>
<p>I would recommend to maybe store the event dates in a more robust way, like the Super Table field you're using for the <code>Festival Dates</code> field. A comma-separated list is inconvenient to search or sort by. If you insist on the comma-separated list, <em>at least</em> use <code>YYYY-MM-DD</code> format for sanity (see <a href="https://en.wikipedia.org/wiki/ISO_8601" rel="nofollow noreferrer">ISO 8601</a>). You can still format the dates differently in the output.</p>
<p>Regarding your second query, once you have the full list, you can also sort it by a specific value using the <a href="https://craftcms.com/docs/3.x/dev/filters.html#append" rel="nofollow noreferrer"><code>sort</code> filter</a>. Assuming the start date is the first date in the comma-separated list, use the sort filter and the <a href="https://twig.symfony.com/doc/3.x/filters/split.html" rel="nofollow noreferrer"><code>split</code> filter</a> to get the first date, then use that for the comparison to sort by date. This will be much easier if you use <code>YYYY-MM-DD</code> format to store the dates, since this format sorts naturally in chronological order.</p>

CC BY-SA 4.0
2023-01-20T09:56:58.460Z
Amazing, thank you! About to test this out.
James Greig
CC BY-SA 4.0
2023-01-20T09:57:51.787Z
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) - https://dubrox.github.io/Multiple-Dates-Picker-for-jQuery-UI
James Greig
CC BY-SA 4.0
2023-01-20T10:22:55.127Z
@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: https://dubrox.github.io/Multiple-Dates-Picker-for-jQuery-UI/#demo-custom-format
MoritzLost
CC BY-SA 4.0
2023-01-20T13:12:06.333Z
@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?
James Smith
CC BY-SA 4.0
2023-01-20T16:58:03.213Z
I agree @JamesSmith but also figured it was quicker and more accurate to do manually than try and figure that out : )
James Greig
CC BY-SA 4.0
2023-01-20T09:44:09.567Z
2023-01-20T09:44:09.567Z
MoritzLost
<p>This loop containing a query is (surprise surprise) proving very intensive.</p>
<p>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.</p>
<p>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?</p>
<p>I've got around this until now by caching the output of the entire templatebut this feels naughty.</p>
<pre><code>{# 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(&quot;+1 day&quot;) &gt;= now ) ? 'true' : 'false' %}

    &lt;tbody class=&quot;list&quot; x-data=&quot;{ open: {{ dateDisplayState }} }&quot;&gt;
        {% set dateFormatted = dateRow.date|date('d-m-Y') %}
        {# Show/hide toggle with AlpineJS #}
        &lt;tr class=&quot;cursor-pointer&quot; @click=&quot;open = !open&quot;&gt;&lt;th&gt;&lt;h3 id=&quot;day{{ loop.index }}&quot; class=&quot;mt-12 mb-2 text-2xl font-normal text-left md:text-4xl&quot;&gt;{{ dateFormatted|date('l d F') }} &lt;span class=&quot;border-black uppercase text-xs&quot; x-text=&quot;open ? 'hide' : 'show'&quot;&gt;&lt;/span&gt;&lt;/h3&gt;&lt;/th&gt;&lt;/tr&gt;
        {% set eventsThisDay = craft.entries()
            .section('events')
            .relatedTo(year)
            .search(&quot;eventDates:#{dateFormatted}&quot;)
            .with('heroImage','eventTypepicker','eventRegion')
            .all()
        %}
        {% for event in eventsThisDay %}
            {% include('_partials/eventRow') with { showRegion: true, showDate: false, showCategory: true } %}
        {% endfor %}
    &lt;/tbody&gt;
{% endfor %}
</code></pre>
<p>And the other query (inside a Sprig component):</p>
<pre><code>{% set events = craft.entries()
    .section('events')
    .with([['heroImage', { kind: 'image'}]],['eventTypepicker'],['eventRegion'])
    .offset(offset)
    .orderBy('startDate asc')
    .limit(limit)
    .relatedTo(year)
    .all()
%}
</code></pre>
<p>Here's an overview of the frontend view and the entries/fields - <code>festivalDates</code> is a Matrix field on the Festival entry with a date in each block, and <code>eventDates</code> is a free text field on the Event entry with a comma-separated list of dates for that event.</p>
<p><a href="https://i.sstatic.net/8C7Vd.jpg" rel="nofollow noreferrer"><img src="https://i.sstatic.net/8C7Vd.jpg" alt="Screenshot of screens" /></a></p>

CC BY-SA 4.0
2023-01-20T08:25:28.157Z
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]
James Greig
<craft3><cache><elementquery><optimization>
Optimise this repeated entry query? (which groups entries by a shared field value)
# Optimise this repeated entry query? (which groups entries by a shared field value)

(Asked by: username_0 on 2023-01-19)

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' %}

    
        {% set dateFormatted = dateRow.date|date('d-m-Y') %}
        {# Show/hide toggle with AlpineJS #}
        {{ dateFormatted|date('l d F') }} 
        {% 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 %}
    
{% 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.

**Tags:** craft3, cache, elementquery, optimization

> **Comments:**
> **username_0**: 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]  

---

## 1 answers

### Answer by username_1 on 2023-01-20. Score: 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.

> **Comments:**
> **username_0**: Amazing, thank you! About to test this out.  
> **username_0**: 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) - https://dubrox.github.io/Multiple-Dates-Picker-for-jQuery-UI  
> **username_1**: @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: https://dubrox.github.io/Multiple-Dates-Picker-for-jQuery-UI/#demo-custom-format  
> **username_2**: @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?  
> **username_0**: I agree @JamesSmith but also figured it was quicker and more accurate to do manually than try and figure that out : )  

---

