How to list all existing values for a field across site

Question

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 %}
    <li>{{ year }}</li>
{% endfor %}

Answer 1

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.

1. Start from fresh using craft()->db->createCommand() and add all the necessary parameters manually (section, locale, limit,...).
2. 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.