2

I have imported data with 18000+ entries. It's a list of product models with brands associated to each. The product model is being used as the title and the brand field is plain text.

There are approximately 140 brands which I need to output as a dropdown.

I can't output this many entries and then try and group (and order them) on the template as it crashes the page.

This is code that would work if it didn't crash the page (and would take minutes to load the page even if it didn't).

{% set allEntries = craft.entries.section('solarWaterHeaterModels').order('brand asc').limit(null).find() %}

{% set allEntriesByBrand = allEntries|group('brand') %}

{% for brand, entriesInBrand in allEntriesByBrand %}

    {% for entry in entriesInBrand %}
        {% if loop.index == 1 %}
            <option value="{{ entry.brand }}">{{ entry.brand }}</option>
        {% endif %}
    {% endfor %}

{% endfor %}

Can anyone offer some guidance on how to approach this please?

Many thanks

2

First, based on your example code I don't think you actually need to loop on the entries – you could simply group them on the brand name, and then loop on the keys for the grouped array (the keys would be your brand names):

{% set brands = allEntries|group('brand')|keys %}

{% for brand in brands %}
     <option value="{{ brand }}">{{ brand }}</option>
{% endfor %}

So that's a slight optimization, but you'd still be pulling ~18k entries to print ~140 brand names and I'm not sure the above wouldn't still crash your server – so ideally you should probably use a different strategy altogether.

If you're comfortable w/ writing a little PHP, I'd suggest you scaffold a custom plugin – make sure that the Variables component is included, and add something like the below to the variables/YourPluginHandleVariable.php class:

public function getBrands()
{
    $query = craft()->db->createCommand();
    $query->selectDistinct('field_brand');
    $query->from('content');
    return array_filter($query->queryColumn());
}

In your template, you'd use this like so:

{% set brands = craft.yourPluginHandle.getBrands() %}

{% for brand in brands %}
     <option value="{{ brand }}">{{ brand }}</option>
{% endfor %}

Be sure to replace yourPluginHandle and YourPluginHandleVariable with the actual handle for your plugin.

Note that I haven't actually tested the above w/ as many as +18k rows, so even a distinct select query might not be performant enough. Of course, you can always cache the result.

  • 1
    I had a feeling a plugin would be the way to go with that volume of entries. This is exactly the guidance I was looking for. Thanks so much! – MikeeBee Sep 26 '17 at 0:37
  • 1
    Awesome. If you get the chance, a report back on the performance would be interesting (if it even works, as I said I've not tested this w/ the volume of entries you have) :) – Mats Mikkel Rummelhoff Sep 26 '17 at 0:38
  • 1
    This worked great. Took around 3.5 seconds to go through 18438 entries. I'm very happy, thank you! – MikeeBee Oct 5 '17 at 2:42
  • That's good to hear, 3.5 secs is still a lot though, if this happens every pageload... – Mats Mikkel Rummelhoff Oct 5 '17 at 7:00
  • Yeah it's still a lot for a page load generally, but compared to the minute plus it was taking, this is a great improvement! And as you say, I can cache the result. These entries will only change every few months. – MikeeBee Oct 5 '17 at 21:26

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.