Challenge #10 – How Low Can You Limbo

14 March 2023 Solved Twig Intermediate

The Limbo World Cham­pi­on­ships have called you in to optim­ise their score­board for performance.

Chal­lenge

On inspect­ing the homepage, you notice that it is sub­op­tim­al, execut­ing 62 data­base quer­ies and pop­u­lat­ing 28 ele­ments, when you can plainly see that only 18 unique ele­ments are actu­ally dis­played on the score­board. Also, as the num­ber of com­pet­it­ors increases, so do the num­ber of quer­ies and ele­ments, mean­ing that the code has a com­plex­ity of O(n) (also known as the N+1 problem).

Scoreboard

There’s no time to lose. Your task is to bring the com­plex­ity of the quer­ies under con­trol and reduce the num­ber of data­base quer­ies executed and ele­ments loaded as much as humanly pos­sible. There have been mur­murs in the limbo com­munity of a rising star that has man­aged to achieve the coveted 35 (data­base quer­ies), but this is yet to be confirmed.

This Git­Hub repo con­tains the Craft CMS site that you can spin up with a single com­mand, either loc­ally or in the browser using a Git­Hub codespace (see the readme file in the repo). Once you are set up, log into the CP as the admin user, vis­it the front-end URL, open the debug tool­bar and then get to work!

Rules

Your solu­tion should con­sist of an optim­ised ver­sion of the templates/index.twig tem­plate, along with men­tion­ing the num­ber of data­base quer­ies executed and ele­ments loaded.

  • Cach­ing may not be used. 
  • You may only edit the templates/index.twig template. 
  • You may over­ride the body block, but doing so shouldn’t be neces­sary or even helpful.
  • You must be logged in as the admin user with the debug tool­bar enabled.
  • No plu­gins or mod­ules may be used except for the Ele­ments Pan­el plu­gin, which is and must remain installed.

Tips

Eager-load all the things and lever­age Twig fil­ters to help avoid queries.

Acknow­ledge­ments

Solution

Since redu­cing the num­ber of data­base quer­ies executed is the primary goal of this chal­lenge, the obvi­ous place to start is with adding eager-load­ing to the ele­ment query. This involves telling Craft which ele­ments to load up-front (in the ini­tial data­base query), sav­ing us hav­ing to fetch those ele­ments later (in sub­sequent data­base queries). 

Craft lazy-loads ele­ments by default, as not doing so could lead to huge amounts of data being unne­ces­sar­ily fetched via data­base quer­ies. Ima­gine, for example, that we wanted to list only the titles of all entries in an entry type that has sev­er­al rela­tion fields (assets, entries, etc.). If Craft eager-loaded every rela­tion field by default then the query (and the returned data) would be much more bloated and much less per­form­ant than load­ing only the simple (non-rela­tion­al fields). There­fore, if we want to list the titles along with related asset image then it is up to us to instruct Craft to eager-load those assets in the ele­ment query.

Start­ing with the com­pet­it­ors, we can see that for each entry we’ll need the title, score, image (related asset) and coun­try flag (a related asset on a related entry). The title and score are non-rela­tion­al fields, so they will always be loaded auto­mat­ic­ally. The image and coun­try are rela­tion­al fields and the flag is a related asset on the coun­try, so we’ll eager-load these using the with para­met­er on the query.

{% set competitors = craft.entries
    .section('competitors')
    .with([
        'image',
        'country.flag',
    ])
    .orderBy('score asc')
    .all()
%}

Sim­il­ar solu­tions: Alex Rop­er, Domin­ik Krulak, Gaël Pat­ron, Liam Rella, Marco, Marko Gajić, Phil­ippe Per­usse, Pio­tr Pogorzel­ski, Robert Baker, Rodrigo Pas­sos.

This change alone brings the num­ber of data­base quer­ies from 62 down to 35!!

One reas­on for this is that competitor.country.one() was pre­vi­ously an ele­ment query exe­cu­tion with­in a for loop, mean­ing that it suffered from O(n) com­plex­ity. 

{% for competitor in competitors %}
    {% set country = competitor.country.one() %}
    {# ... #}
{% endfor %}

When we eager-loaded the country’s flag using country.flag, this also eager-loaded the coun­try (by neces­sity). So where­as competitor.country pre­vi­ously rep­res­en­ted an ele­ment query, it now rep­res­ents an eager-loaded Lara­vel Col­lec­tion of coun­tries. Col­lec­tions in Craft have a one() macro that calls first() intern­ally, so call­ing competitor.country.one() simply returns the first item in the col­lec­tion of coun­tries – no DB calls required.

Inter­est­ingly, though, while the num­ber of data­base quer­ies has decreased, the num­ber of ele­ments loaded has increased from 28 to 38! This is because we are now fetch­ing com­pet­it­ors along with eager-loaded ele­ments for every single entry in the competitors sec­tion. Lim­it­ing this num­ber to the num­ber of com­pet­it­ors we want to out­put should solve the issue.

{% set topCompetitors = craft.entries
    .section('competitors')
    .with([
        'country.flag',
        'image',
    ])
    .orderBy('score asc')
    .limit(5)
    .all()
%}

Sim­il­ar solu­tions: Har­ald Attene­der.

This indeed does bring the num­ber of loaded ele­ments down to 18, but it also presents a ​“gotcha”, which we’ll come back to later. 

With the limit para­met­er in place, there is now no need to call competitors|slice(0, 5) since our query can return at most 5 entries. We can also clean up the for loop used to fetch the top 3 unique coun­tries using two approaches: Twig fil­ters and Col­lec­tion methods.

Twig Fil­ters

Twig fil­ters will likely be more famil­i­ar to most people, since they have been avail­able in Craft since forever. There are mul­tiple approaches using fil­ters that can be taken to reduce the coun­tries to a unique set of 3. Here is my pre­ferred approach.

{% set topCountries = topCompetitors
    |map(competitor => competitor.country.one())
    |unique
    |slice(0, 3)
%}

Sim­il­ar solu­tions: Andrew Welch, Aus­tin Siew­ert.

Col­lec­tion Methods

Execut­ing ele­ment quer­ies with .collect() instead of .all() returns a Col­lec­tion of ele­ments, which makes it is pos­sible to use the many meth­ods that Col­lec­tions provide. The only caveat is that Twig only allows arrow func­tions to be used in the map, filter and reduce fil­ters. This makes some Col­lec­tion meth­ods less use­ful (or com­pletely unus­able), unless you install the Craft Clos­ure pack­age, in which case you can use arrow func­tion clos­ures everywhere!

In the fol­low­ing approach we pluck the country out of each of the topCompetitors, col­lapse the res­ult into a flat col­lec­tion of coun­tries, reduce the res­ult to unique items based on the coun­try id, and finally take just the first 3 items. Note how chain­ing meth­ods is pos­sible because each meth­od returns a new Col­lec­tion of mod­i­fied results.

{% set topCountries = topCompetitors
    .pluck('country')
    .collapse()
    .unique('id')
    .take(3)
%}

Sim­il­ar solu­tions: Amber Wal­tens, Chris Viol­ette, Dave Spengel­er, Johannes Ahrndt.

The reas­on that call­ing .collapse() is neces­sary above is that the country field is itself a Col­lec­tion, since it was eager-loaded in the ele­ment query. There­fore .pluck('country') returns a Col­lec­tion of Col­lec­tions, which we must col­lapse into a Col­lec­tion of entries before call­ing the unique method. 

If Craft Clos­ure was installed then we would be able to refact­or the code to resemble the Twig fil­ter approach more closely using the map meth­od, which is more expli­cit and some­what more rep­res­ent­at­ive of what is going on behind the scenes.

{% set topCountries = topCompetitors
    .map(competitor => competitor.country.one())
    .unique('id')
    .take(3)
%}

The Gotcha

We men­tioned a ​“gotcha” and it is this: What hap­pens if the top 5 com­pet­it­ors come from only 1 or 2 unique coun­tries? If that’s the case then the top coun­tries will dis­play few­er than 3 coun­tries. There are two approaches to solv­ing this edge-case: fetch­ing all com­pet­it­ors upfront or con­di­tion­ally fetch­ing more competitors.

Fetch­ing all com­pet­it­ors upfront will res­ult in more ele­ments being loaded but the same num­ber of data­base quer­ies being executed – it is, in fact, the first solu­tion presen­ted above. Con­di­tion­ally fetch­ing more com­pet­it­ors, how­ever, would only actu­ally hap­pen in the scen­ario in which the top 5 com­pet­it­ors come from just 1 or 2 unique coun­tries. In oth­er words, the second approach will be more per­form­ant most of the time.

There are vari­ous ways we could go about this, but here’s a simple approach that is only executed if few­er than 3 coun­tries exist in topCountries.

{% set topCountries = topCompetitors
    |map(competitor => competitor.country.one())
    |unique
    |slice(0, 3)
%}

{% if topCountries|length < 3 %}
    {% set allCompetitors = craft.entries
        .section('competitors')
        .with('country.flag')
        .orderBy('score asc')
        .all()
    %}
    {% set topCountries = allCompetitors
        |map(competitor => competitor.country.one())
        |unique
        |slice(0, 3)
    %}
{% endif %}

Sim­il­ar solu­tions: Eddie Dale.

The fol­low­ing approach res­ults in few­er ele­ments being loaded by off­set­ting the com­pet­it­ors pre­vi­ously fetched and mer­ging the remain­ing coun­tries with the exist­ing top countries.

{% set topCountries = topCompetitors
    |map(competitor => competitor.country.one())
    |unique
    |slice(0, 3)
%}

{% if topCountries|length < 3 %}
    {% set remainingCompetitors = craft.entries
        .section('competitors')
        .with('country.flag')
        .orderBy('score asc')
        .offset(5)
        .all()
    %}
    {% set remainingCountries = remainingCompetitors
        |map(competitor => competitor.country.one())
    %}
    {% set topCountries = topCountries
        |merge(remainingCountries)
        |unique
        |slice(0, 3)
    %}
{% endif %}

Sim­il­ar solu­tions: Lukas Jansen.

Chat­G­PT Hype

Since Chat­G­PT is all the hype these days, I decided to see how it would do, giv­en the chal­lenge (I prom­ise this will be the only time I ever try this!). The bot struggled, always com­ing back to execut­ing one query for the com­pet­it­ors and anoth­er for the coun­tries (and insisted that this was the most per­form­ant solu­tion). It took over an hour of coach­ing, but I finally got it to arrive at a present­able solution.

ChatGPT solution

Tak­ing it Further

There are ways of redu­cing the num­ber of data­base quer­ies even fur­ther, but they quickly get com­plex, fra­gile and dif­fi­cult to main­tain. Nev­er­the­less, I’ll leave you with this solu­tion by Steve Com­rie that requires only 19 data­base quer­ies through bypassing ele­ment quer­ies com­pletely and using Craft’s Query class to manu­ally join tables instead (not recom­men­ded but inter­est­ing to see!).

Submitted Solutions

  • Marco
  • Andrew Welch
  • Robert Baker
  • Chris Violette
  • Austin Siewert
  • Lukas Jansen
  • Marko Gajić
  • Steve Comrie
  • Rodrigo Passos
  • Johannes Ahrndt
  • Philippe Perusse
  • Amber Waltens
  • Eddie Dale
  • Dominik Krulak
  • Harald Atteneder
  • Liam Rella
  • Dave Spengeler
  • Gaël Patron
  • Piotr Pogorzelski
  • Alex Roper
Created and maintained by PutYourLightsOn © 2023