Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-07-18T16:16:30.807Z
Answers.Text: 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?
Answers.UserDisplayName: Terry Upton
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-07-19T19:46:35.523Z
Answers.Text: 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 %}`
Answers.UserDisplayName: Gogster
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-07-19T19:57:20.450Z
Answers.Text: 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 %}`
Answers.UserDisplayName: Gogster
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-07-20T14:31:41.683Z
Answers.Text: Thanks for the reply. However, I am seeing the error; The merge filter only works with arrays or "Traversable", got "NULL" as second argument.
Answers.UserDisplayName: Terry Upton
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-07-20T15:04:29.687Z
Answers.Text: 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
Answers.UserDisplayName: Gogster
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-08-13T08:55:52.720Z
Answers.LastActivityDate: 2015-08-13T09:01:57.067Z
Answers.LastEditDate: 2015-08-13T09:01:57.067Z
Answers.UserDisplayName: Gogster
Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-08-12T12:32:11.197Z
Answers.Text: 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.
Answers.UserDisplayName: Gogster
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-08-12T12:40:40.243Z
Answers.Text: 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.
Answers.UserDisplayName: Paul
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-08-12T14:35:58.980Z
Answers.Text: That's what I wanted to know, thanks. Will have to get my hands dirty with a plugin then!
Answers.UserDisplayName: Gogster
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-08-12T14:36:53.047Z
Answers.Text: Let me know if you need any advice!
Answers.UserDisplayName: Paul
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-08-12T16:08:36.763Z
Answers.Text: The plugin worked a treat, much more efficient. Thanks again for the pointer.
Answers.UserDisplayName: Gogster
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-08-12T16:59:36.973Z
Answers.Text: Mind posting your new solution / answer (and accept it), @Gogster?
Answers.UserDisplayName: carlcs
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-08-12T11:48:54.487Z
Answers.LastActivityDate: 2015-08-12T11:48:54.487Z
Answers.UserDisplayName: Paul
Body: <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>
  
Tags: <templating><entries>
Title: Omitting unused letters from A to Z list
ThreadText: # 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`:
  
  ```
  <?php
  namespace Craft;
  class MyPluginVariable
  {
  public function getAToZ()
  {
      return craft()->myPlugin_members->getAToZ();
  }
  }
  
  ```
  
  And the other in `services` subfolder `MyPlugin_MembersService.php`:
  
  ```
  <?php
  namespace Craft;
  
  class MyPlugin_MembersService extends BaseApplicationComponent
  {
  public function getAToZ()
  {   
      $query = craft()->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)') %}
      <h1>{{ letter }}</h1>
      <ul>
      {% for member in members %}
          <li>{{ member.surname }}, {{ member, firstname }}</li>
      {% endfor %}
      </ul>
  {% 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), @username_0?  
  
  ---
  
  