<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>

CC BY-SA 3.0
2016-01-31T12:08:34.233Z
2016-01-31T12:08:34.233Z
carlcs
<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>

CC BY-SA 3.0
2016-02-02T14:49:46.397Z
Please let me know if there's anything unclear with my answer @certainlyakey.
carlcs
CC BY-SA 3.0
2016-02-02T15:51:52.447Z
Thank you @carics. I think everything is clear, I just need to test it. Is the variables subfolder enough to make the plugin work?
certainlyakey
CC BY-SA 3.0
2016-02-02T15:55:40.957Z
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/
carlcs
<entry><field><loop>
How to list all existing values for a field across site
# 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 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 %}
    {{ year }}
{% 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
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 to create your SQL statements. There's now two ways to approach this.

Start from fresh using craft()->db->createCommand() and add all the necessary parameters manually (section, locale, limit,...).
Pass a Craft criteria model with all parameters already set to the buildElementsQuery 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 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.


---

