1

I have a Twig include that generates a News Archive side-bar. It works great, but I've just noticed that on a particular project it's contributing in the region of 800 DB Queries when caching is off.

What have I missed about the mechanics of the group filter? Because I can see no other reason why this should generate more than one query?

{% cache globally %}
    {% set allEntries = craft.entries.section('newsItems').limit(null).all() %}
    <div class="archives widget">
        <h2>News Archive</h2>
        <ul>
            {% for year, entriesInYear in allEntries | group("postDate | date('Y')") %}
                <li>
                    <a href="/news/archive/{{ year }}">{{ year }}</a>
                    <ul>
                        {% for month, entriesInMonth in entriesInYear | group("postDate|date('m')") | reverse(true) %}
                            <li>
                                <a href="/news/archive/{{ year }}/{{ month }}">
                                    {{ entriesInMonth[0].postDate|date('F') }}
                                </a>
                            </li>
                        {% endfor %}
                    </ul>
                </li>
            {% endfor %}
        </ul>
    </div><!-- .archives -->
{% endcache %}
1

The solution is below. EDIT: only for MySQL 5.7.4 and lower! Higher versions break.

For whatever reason, the mechanics of the group filter in this case was producing a new query for every Entry. Below, resolves this by doing the same job in a more performant way. I'm not entirely sure of the mechanics of how this works, but the answer was supplied over on the Discord support channel and I thought it worth cross-posting to here. Thanks Sebastian Lenz!

{% cache globally %}
    {% set postDates = craft.entries({
        section: 'newsItems',
        select: 'postDate',
        limit: null,
        groupBy: ['DATE_FORMAT(postDate, "%Y-%m")'],
    }).column() %}

    <div class="archives widget">
        <h2>News Archive</h2>
        <ul>
            {% for year, postDatesInYear in postDates|group("object|date('Y')") %}
                <li>
                    <a href="/news/archive/{{ year }}">{{ year }}</a>
                    <ul>
                        {% for month, postDatesInMonth in postDatesInYear|group("object|date('m')")|reverse(true) %}
                            <li>
                                <a href="/news/archive/{{ year }}/{{ month }}">
                                    {{ postDatesInMonth[0]|date('F') }}
                                </a>
                            </li>
                        {% endfor %}
                    </ul>
                </li>
            {% endfor %}
        </ul>
    </div><!-- .archives -->
{% endcache %}
  • NOTE: This appears to only work with MySQL versions below 5.7.5. – Matt Wilcox Sep 18 at 15:11

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.