How-to library · Developer docs

How-to library

Task-shaped pages: a title that starts with a verb, the situation in a few lines, the steps, the working code in both engines, and why this way rather than another. Found from a search, done, and left.

How-to Performance, deployment and operations

Remove a query-per-row from a template loop

Published

A lookup inside a loop runs once per row. Collect the keys first, fetch every record in one query, and read from a map inside the loop.

Context

Context. A listing of team members shows each person's office, stored on the card as the office's slug, and the template looks the office up for every card. With forty cards that is forty queries, and the page renders more slowly the longer the team gets. Handle it the same way for any lookup inside a loop: fetch everything once, before the loop, and look it up inside. It applies to $_query, and the pattern is the same for any helper that queries on each call.

Steps

  1. Find the lookup inside the loop: a $_query call, or any helper call, that takes a value from the row (a lookup helper in a loop).
  2. Collect the keys in a first loop over the same rows.
  3. Fetch once with the | (in) operator on the key, guarded so that an empty list never reaches the query.
  4. Index the result by the key. In Twig, give the key a text prefix.
  5. Read from the index inside the loop, and handle a key with no match.
  6. Time the page before and after on the same environment (find out why a page is slow).

Code

Before, one query per card:

Template code Smarty only 
{foreach $page->getAreaCollectionElements('team') as $card}
  {$office = $_query->findOneBy('Common\Entities\Category', ['slug' => $card->getAreaText('office')])}
  <li>{$card->getAreaText('name')|escape}{if $office}, {$office->getName()|escape}{/if}</li>
{/foreach}

After, one query for the whole list:

Template code 
{$team = $page->getAreaCollectionElements('team')}
{$slugs = []}
{foreach $team as $card}{$slugs[] = $card->getAreaText('office')}{/foreach}

{$offices = []}
{if $slugs}
  {foreach $_query->findBy('Common\Entities\Category', ['|slug' => $slugs]) as $office}
    {$offices[$office->getSlug()] = $office}
  {/foreach}
{/if}

{foreach $team as $card}
  {$slug = $card->getAreaText('office')}
  <li>{$card->getAreaText('name')|escape}{if isset($offices[$slug])}, {$offices[$slug]->getName()|escape}{/if}</li>
{/foreach}
{% set team = page.getAreaCollectionElements('team') %}
{% set slugs = [] %}
{% for card in team %}{% set slugs = slugs|merge([card.getAreaText('office')]) %}{% endfor %}

{% set offices = {} %}
{% if slugs is not empty %}
  {% for office in _query.findBy('Common\\Entities\\Category', {'|slug': slugs}) %}
    {% set offices = offices|merge({('o-' ~ office.getSlug()): office}) %}
  {% endfor %}
{% endif %}

{% for card in team %}
  {% set key = 'o-' ~ card.getAreaText('office') %}
  <li>{{ card.getAreaText('name')|e }}{% if offices[key] is defined %}, {{ offices[key].getName()|e }}{% endif %}</li>
{% endfor %}

Why this and not that

The guard matters: an in filter with an empty list is refused, and the page fails instead of rendering an empty list. The 'o-' prefix in Twig matters too: merge renumbers numeric keys, so give every key a text prefix whenever a slug or id could be a bare number, or the lookup misses after the keys are renumbered. Fetching all categories with no filter would also be one query, and is fine for a short list, but it grows with the site rather than with the page. And caching the page does not make the loop free: it makes the cost rarer, and a page that renders for every member or after every save still pays it (is it worth it).

Go deeper
GuideFetching once instead of per iteration GuideThe three query-per-row shapes

Was this page helpful?

Your answer goes to the docs team.