Answers.Body: <p>To get better performance you need to switch to using PHP. All you need is a <a href="https://craftcms.com/docs/plugins/variables" rel="nofollow">MyPluginVariable class</a>
  to do some logic in PHP and make the result available to your Twig templates. It's really not very complicated.</p>
  
  <p>From your variable class you'd use <a href="https://craftcms.com/docs/plugins/database" rel="nofollow">Yii Query Builder</a> to create your SQL statements. There's now two ways to approach this.</p>
  
  <ol>
  <li>Start from fresh using <a href="https://craftcms.com/docs/plugins/database" rel="nofollow"><code>craft()-&gt;db-&gt;createCommand()</code></a> and add all the necessary parameters manually (section, locale, limit,...).</li>
  <li>Pass a Craft <a href="https://craftcms.com/docs/templating/elementcriteriamodel" rel="nofollow">criteria model</a> with all parameters already set to the <a href="http://buildwithcraft.com/classreference/services/ElementsService#buildElementsQuery-detail" rel="nofollow"><code>buildElementsQuery</code></a> method to convert it.</li>
  </ol>
  
  <p>I'll show you exampe code for the second approach. From your template prepare a criteria model as usual, set the section and other parameters. Then call the new template function we are going to create soon, which will take two parameters, a criteria model and a field handle.</p>
  
  <pre><code>{% set criteria = craft.entries.section('books').order('myYearsField desc').limit(null) %}
  {% set years = craft.businessLogic.getDistinctValues(criteria, 'myYearsField') %}
  </code></pre>
  
  <p>Now we need to create this <code>getDistinctValues</code> method in our variable class.</p>
  
  <pre><code>public function getDistinctValues($criteria, $column)
  {
      $query = craft()-&gt;elements-&gt;buildElementsQuery($criteria);
  
      // Modify query to select just our `$column` column, only distinct values
      $query-&gt;selectDistinct("field_{$column}");
  
      // Query for one column only and return the result
      return $query-&gt;queryColumn();
  }
  </code></pre>
  
  <p>That <a href="http://www.yiiframework.com/doc/api/1.1/CDbCommand#selectDistinct-detail" rel="nofollow"><code>selectDistinct</code></a> parameter overrides the <code>select</code> set by Craft in the initial criteria model. Performance should be way better, you're querying for a single column only, only for distinct values and you don't populate entry models which you do not need anyways.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-01-31T12:08:34.233Z
Answers.LastActivityDate: 2016-01-31T12:08:34.233Z
Answers.UserDisplayName: carlcs
Body: <p>I have a huge set of entries which have a lot of fields. Say, the entry type is a 'book' and fields are 'keywords', 'year', 'authors', etc. </p>
  
  <p>Now I'm building a filter form which would allow user to output entries by field values. On frontend side this form contains generally inputs with datalist tags which should contain all of a specific field's values. For example, the datalist for the 'Year' search input should contain all years ever set for all the books on the site.</p>
  
  <p>How do I do this efficiently, how do we list all existing values for a field? <a href="https://craftcms.stackexchange.com/a/5102/3838">This answer</a> proposes to loop through each entry every time and collect the field values used in entries in an array - but when I tried to do it I hit the memory exhausted problem. </p>
  
  <p>An example code of the approach which leads to memory being exhausted:</p>
  
  <pre><code>{% set years = [] %}
  {% for book in craft.entries.section('books').year.order('year desc').limit(0) %}
      {% if book.year not in years %}
          {% set years = years|merge([book.year]) %}
      {% endif %}
  {% endfor %}
  
  {% for year in years %}
      &lt;li&gt;{{ year }}&lt;/li&gt;
  {% endfor %}
  </code></pre>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2016-02-02T14:49:46.397Z
Comments.Text: Please let me know if there's anything unclear with my answer @certainlyakey.
Comments.UserDisplayName: carlcs
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2016-02-02T15:51:52.447Z
Comments.Text: Thank you @carics. I think everything is clear, I just need to test it. Is the variables subfolder enough to make the plugin work?
Comments.UserDisplayName: certainlyakey
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2016-02-02T15:55:40.957Z
Comments.Text: Yes, a [primary plugin class](https://craftcms.com/docs/plugins/setting-things-up#your-primary-plugin-class) and a [template variable class](https://craftcms.com/docs/plugins/variables) in variables/
Comments.UserDisplayName: carlcs
Tags: <entry><field><loop>
Title: How to list all existing values for a field across site
ThreadText: # How to list all existing values for a field across site
  
  (Asked by: username_0 on 2016-01-30)
  
  I have a huge set of entries which have a lot of fields. Say, the entry type is a 'book' and fields are 'keywords', 'year', 'authors', etc.
  
  Now I'm building a filter form which would allow user to output entries by field values. On frontend side this form contains generally inputs with datalist tags which should contain all of a specific field's values. For example, the datalist for the 'Year' search input should contain all years ever set for all the books on the site.
  
  How do I do this efficiently, how do we list all existing values for a field? [This answer](https://craftcms.stackexchange.com/a/5102/3838) proposes to loop through each entry every time and collect the field values used in entries in an array - but when I tried to do it I hit the memory exhausted problem.
  
  An example code of the approach which leads to memory being exhausted:
  
  ```
  {% set years = [] %}
  {% for book in craft.entries.section('books').year.order('year desc').limit(0) %}
      {% if book.year not in years %}
          {% set years = years|merge([book.year]) %}
      {% endif %}
  {% endfor %}
  
  {% for year in years %}
      <li>{{ year }}</li>
  {% endfor %}
  
  ```
  
  **Tags:** entry, field, loop
  
  > **Comments:**
  > **username_1**: Please let me know if there's anything unclear with my answer @username_0.  
  > **username_0**: Thank you @carics. I think everything is clear, I just need to test it. Is the variables subfolder enough to make the plugin work?  
  > **username_1**: Yes, a [primary plugin class](https://craftcms.com/docs/plugins/setting-things-up#your-primary-plugin-class) and a [template variable class](https://craftcms.com/docs/plugins/variables) in variables/  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2016-01-31. Score: 2
  
  To get better performance you need to switch to using PHP. All you need is a [MyPluginVariable class](https://craftcms.com/docs/plugins/variables)
  to do some logic in PHP and make the result available to your Twig templates. It's really not very complicated.
  
  From your variable class you'd use [Yii Query Builder](https://craftcms.com/docs/plugins/database) to create your SQL statements. There's now two ways to approach this.
  
  1. Start from fresh using [`craft()->db->createCommand()`](https://craftcms.com/docs/plugins/database) and add all the necessary parameters manually (section, locale, limit,...).
  2. Pass a Craft [criteria model](https://craftcms.com/docs/templating/elementcriteriamodel) with all parameters already set to the [`buildElementsQuery`](http://buildwithcraft.com/classreference/services/ElementsService#buildElementsQuery-detail) method to convert it.
  
  I'll show you exampe code for the second approach. From your template prepare a criteria model as usual, set the section and other parameters. Then call the new template function we are going to create soon, which will take two parameters, a criteria model and a field handle.
  
  ```
  {% set criteria = craft.entries.section('books').order('myYearsField desc').limit(null) %}
  {% set years = craft.businessLogic.getDistinctValues(criteria, 'myYearsField') %}
  
  ```
  
  Now we need to create this `getDistinctValues` method in our variable class.
  
  ```
  public function getDistinctValues($criteria, $column)
  {
      $query = craft()->elements->buildElementsQuery($criteria);
  
      // Modify query to select just our `$column` column, only distinct values
      $query->selectDistinct("field_{$column}");
  
      // Query for one column only and return the result
      return $query->queryColumn();
  }
  
  ```
  
  That [`selectDistinct`](http://www.yiiframework.com/doc/api/1.1/CDbCommand#selectDistinct-detail) parameter overrides the `select` set by Craft in the initial criteria model. Performance should be way better, you're querying for a single column only, only for distinct values and you don't populate entry models which you do not need anyways.
  
  
  ---
  
  