<p>The answer is that it is possible in Twig but not very efficient so I ended up writing a very simple plugin with just two files (aside from the main plugin file).</p>

<p>One in <code>variables</code> subfolder <code>MyPluginVariable.php</code>:</p>

<pre><code>&lt;?php
namespace Craft;
class MyPluginVariable
{
public function getAToZ()
{
    return craft()-&gt;myPlugin_members-&gt;getAToZ();
}
}
</code></pre>

<p>And the other in <code>services</code> subfolder <code>MyPlugin_MembersService.php</code>:</p>

<pre><code>&lt;?php
namespace Craft;

class MyPlugin_MembersService extends BaseApplicationComponent
{
public function getAToZ()
{   
    $query = craft()-&gt;db-&gt;createCommand()
        -&gt;selectDistinct('SUBSTRING(field_surname,1,1) AS letter')
        -&gt;from('content')
        -&gt;order('letter asc')
        -&gt;queryAll();
    return($query);
}
}
</code></pre>

<p>Then I could access the data in my template:</p>

<pre><code>{% for letter in craft.myPlugin.getAToZ() %}
{{ letter['letter'] }}
</code></pre>

<p>To loop over the letters. There is a very noticeable improvement in performance doing it this way.</p>

CC BY-SA 3.0
2017-07-18T16:16:30.807Z
Looking to implement something similar myself, I have the above working. But ideally I would like to output all a-z and then add a class of disabled to any letters that don't have entries...any ideas how to achieve this?
Terry Upton
CC BY-SA 3.0
2017-07-19T19:46:35.523Z
I'd suggest something like using a loop for all characters and then checking within that if it exists in the db e.g. (untested) `{% set letters = craft.myPlugin.getAToZ()%}{% for letter in 'a'..'z' %}{% if letter in letters%} enable {% else %} disable {endif %}{% endfor %}`
Gogster
CC BY-SA 3.0
2017-07-19T19:57:20.450Z
This is probably more accurate... `{% set letters = [] %}{% for letter in craft.myPlugin.getAToZ() %}{% set letters = letters|merge(letter['letter']) %}{% endfor %} {% for letter in 'a'..'z' %}{% if letter in letters %} enable {% else %} disable {endif %}{% endfor %}`
Gogster
CC BY-SA 3.0
2017-07-20T14:31:41.683Z
Thanks for the reply. However, I am seeing the error; The merge filter only works with arrays or "Traversable", got "NULL" as second argument.
Terry Upton
CC BY-SA 3.0
2017-07-20T15:04:29.687Z
Change `{% set letters = letters|merge(letter['letter']) %}` to `{% set letters = letters|merge([letter['letter']]) %}` - also, just be aware that you may need to use `{% for letter in 'A'..'Z' %}` in order to match case
Gogster
CC BY-SA 3.0
2015-08-13T08:55:52.720Z
2015-08-13T09:01:57.067Z
2015-08-13T09:01:57.067Z
Gogster
<p>[untested]</p>

<p>Something like this should do the trick:</p>

<pre><code>{% for letter, members in craft.entries.section('members').order('surname', 'firstname') | group('surname|slice(0,1)') %}
    &lt;h1&gt;{{ letter }}&lt;/h1&gt;
    &lt;ul&gt;
    {% for member in members %}
        &lt;li&gt;{{ member.surname }}, {{ member, firstname }}&lt;/li&gt;
    {% endfor %}
    &lt;/ul&gt;
{% endfor %}
</code></pre>

CC BY-SA 3.0
2015-08-12T12:32:11.197Z
Thanks, that does work although I had to add `limit(null)` in order to get all results, Craft must have a natural limit to entries returned. However, under the hood this is returning all of the members and their data which has a very noticeable effect on the page load time. I'm looking for a method which just returns a list/array of the letters from the db. I know caching will help but seems a heavy handed approach to pull out all of the data for this purpose.
Gogster
CC BY-SA 3.0
2015-08-12T12:40:40.243Z
Default limit is 100, so limit(null) will do the trick. What you want, can not be done (I think) through Twig. You can get an array of ids by adding .ids() to you craft.entries query, but then you loose the ability to group by first letter of surname. I think creating a plugin with a little bit of logic will do the trick. All you need within your plugin is a variable and a service. Service gets the info you want with one DB query and returns it to Twig.
Paul
CC BY-SA 3.0
2015-08-12T14:35:58.980Z
That's what I wanted to know, thanks. Will have to get my hands dirty with a plugin then!
Gogster
CC BY-SA 3.0
2015-08-12T14:36:53.047Z
Let me know if you need any advice!
Paul
CC BY-SA 3.0
2015-08-12T16:08:36.763Z
The plugin worked a treat, much more efficient. Thanks again for the pointer.
Gogster
CC BY-SA 3.0
2015-08-12T16:59:36.973Z
Mind posting your new solution / answer (and accept it), @Gogster?
carlcs
CC BY-SA 3.0
2015-08-12T11:48:54.487Z
2015-08-12T11:48:54.487Z
Paul
<p>I'm displaying lists of members grouped by their surname initial which the user navigates using a list of letters from A to Z. This part was straightforward enough and the following code works to return all members with a particular initial.</p>

<pre><code>{% set searchString = 'surname:' ~ searchLetter ~ '*' %}
{% set members = craft.entries.section('members').search(searchString).order('surname', 'firstName') %}
</code></pre>

<p>Where I'm struggling is listing only the letters for which there are members to use for navigation, or to be able to list them all but disable those that don't have members.</p>

<p>Currently I just loop over the characters in the alphabet and output all of them.</p>

<pre><code>{% set characters = 'a'..'z' %}
</code></pre>

<p>If someone clicks on a letter that has no members they get a blank list, but this isn't ideal as it would be better if there wasn't a link to this page.</p>

<p>I know with SQL I could do something like a <code>SELECT DISTINCT</code> with grouping to return just a list of initials in use, but is this possible with Twig or would I need to look at writing a plugin?</p>

<templating><entries>
Omitting unused letters from A to Z list
# Omitting unused letters from A to Z list

(Asked by: username_0 on 2015-08-12)

I'm displaying lists of members grouped by their surname initial which the user navigates using a list of letters from A to Z. This part was straightforward enough and the following code works to return all members with a particular initial.
{% set searchString = 'surname:' ~ searchLetter ~ '*' %}
{% set members = craft.entries.section('members').search(searchString).order('surname', 'firstName') %}

Where I'm struggling is listing only the letters for which there are members to use for navigation, or to be able to list them all but disable those that don't have members.
Currently I just loop over the characters in the alphabet and output all of them.
{% set characters = 'a'..'z' %}

If someone clicks on a letter that has no members they get a blank list, but this isn't ideal as it would be better if there wasn't a link to this page.
I know with SQL I could do something like a SELECT DISTINCT with grouping to return just a list of initials in use, but is this possible with Twig or would I need to look at writing a plugin?

**Tags:** templating, entries

---

## 2 answers

### Answer by username_0 on 2015-08-13. Score: 4 | Accepted answer

The answer is that it is possible in Twig but not very efficient so I ended up writing a very simple plugin with just two files (aside from the main plugin file).
One in variables subfolder MyPluginVariable.php:
myPlugin_members->getAToZ();
}
}

And the other in services subfolder MyPlugin_MembersService.php:
db->createCommand()
        ->selectDistinct('SUBSTRING(field_surname,1,1) AS letter')
        ->from('content')
        ->order('letter asc')
        ->queryAll();
    return($query);
}
}

Then I could access the data in my template:
{% for letter in craft.myPlugin.getAToZ() %}
{{ letter['letter'] }}

To loop over the letters. There is a very noticeable improvement in performance doing it this way.

> **Comments:**
> **username_1**: Looking to implement something similar myself, I have the above working. But ideally I would like to output all a-z and then add a class of disabled to any letters that don't have entries...any ideas how to achieve this?  
> **username_0**: I'd suggest something like using a loop for all characters and then checking within that if it exists in the db e.g. (untested) `{% set letters = craft.myPlugin.getAToZ()%}{% for letter in 'a'..'z' %}{% if letter in letters%} enable {% else %} disable {endif %}{% endfor %}`  
> **username_0**: This is probably more accurate... `{% set letters = [] %}{% for letter in craft.myPlugin.getAToZ() %}{% set letters = letters|merge(letter['letter']) %}{% endfor %} {% for letter in 'a'..'z' %}{% if letter in letters %} enable {% else %} disable {endif %}{% endfor %}`  
> **username_1**: Thanks for the reply. However, I am seeing the error; The merge filter only works with arrays or "Traversable", got "NULL" as second argument.  
> **username_0**: Change `{% set letters = letters|merge(letter['letter']) %}` to `{% set letters = letters|merge([letter['letter']]) %}` - also, just be aware that you may need to use `{% for letter in 'A'..'Z' %}` in order to match case  

---

### Answer by username_2 on 2015-08-12. Score: 7

[untested]
Something like this should do the trick:
{% for letter, members in craft.entries.section('members').order('surname', 'firstname') | group('surname|slice(0,1)') %}
    {{ letter }}
    
    {% for member in members %}
        {{ member.surname }}, {{ member, firstname }}
    {% endfor %}
    
{% endfor %}

> **Comments:**
> **username_0**: Thanks, that does work although I had to add `limit(null)` in order to get all results, Craft must have a natural limit to entries returned. However, under the hood this is returning all of the members and their data which has a very noticeable effect on the page load time. I'm looking for a method which just returns a list/array of the letters from the db. I know caching will help but seems a heavy handed approach to pull out all of the data for this purpose.  
> **username_2**: Default limit is 100, so limit(null) will do the trick. What you want, can not be done (I think) through Twig. You can get an array of ids by adding .ids() to you craft.entries query, but then you loose the ability to group by first letter of surname. I think creating a plugin with a little bit of logic will do the trick. All you need within your plugin is a variable and a service. Service gets the info you want with one DB query and returns it to Twig.  
> **username_0**: That's what I wanted to know, thanks. Will have to get my hands dirty with a plugin then!  
> **username_2**: Let me know if you need any advice!  
> **username_0**: The plugin worked a treat, much more efficient. Thanks again for the pointer.  
> **username_3**: Mind posting your new solution / answer (and accept it), @Gogster?  

---

