Template query: Advanced section query to get unique items from custom entry type field

Question

I'm trying to build a filter for a blog section in Craft 3.

I'm fairly new to Craft and my client now wants a dropdown filter where they can filter out authors. Thus, I want to write a query that fetches a unique list of authors from my blog channel structure.

The structure is: section: blogEntries (Channel) with an entry type blogEntry.

Each blogEntry has a content (matrix) and and writer entry field which is connected to one or more employee entries.

So; blogEntries > blogEntry > writer(s) > employee

What I want is something like

craft.entries.section('blogEntries').column('writer').distinct().all()

This is what I have:

{# Way too slow #}
{% for item in craft.entries.section('blogEntries').all() %}
    {% if item.writer|length %}
        {% set person = item.writer.one() %}
        {% set name =  person.firstname ~ (person.surname|length ? ' ' ~ person.surname : '') %}
        {% if name not in writers %}
            {% set writers = writers|merge([name]) %}
        {% endif %}
    {% endif %}
{% endfor %}

Terrible solution, terrible performance.

How can I improve this code? Any ideas would be appreciated.

Answer 1

Try this:

// Return all ids for your posts' section
{% set postIds = craft.entries.section('blogEntries').ids() %}

// Look for and return employees which have a relation with any of the posts returned in 'postIds'
{% set writers = craft.entries.section('employee').relatedTo({ sourceElement: entryIds }).all() %}

// Loop and display
{% for writer in writers %}
    {{ writer.title }} <br>
{% endfor %}

This will only return a list of writers who have written a post, not a list of all writers but I guess that's what you want..?