How to optimize nested reverse relations?

Question

I have the following situation:

A section called "membership" and a section called "member".
Within the "member" section I can choose one or more memberships.
Now I'd like to display all memberships with the reverse related members.

The output would look something like this:

membership 1
    member 1, member 2, member 3

membership 2
    member 2, member 4

membership 3
    member 1, member 5

How would I do this?

I have the following code, but it's very slow:

{# 

    Get all membership entries 

#}
{% set entries = craft.entries({
    section: 'membership',
    order: 'cf_membershipShorthand',
}) %}

{% for entry in entries %}
    <h2><a class="external" href="{{ entry.cf_membershipLink }}">{% if entry.cf_membershipShorthand %}{{ entry.cf_membershipShorthand }} – {% endif %}{{ entry.title }}</a></h2>
    {# 

        Get the related members entries

    #}
    {% set members = craft.entries({
            section: 'member',
            relatedTo: entry,
        })
    %}
    {% if members|length %}
        {% for member in members %}
            {% set image = member.cf_ma_asset[0] ?? null %}
            {% if image|length %}
                {% set imageParams = { width: 30, height: 30 } %}
                <figure class="mini_portrait">
                    <a href="{{ member.getUrl() }}" title="{{ member.title }}"><img class="mini_portrait_image" src="{{ image.getUrl(imageParams) }}" alt="Portrait {{ image.title }}"></img></a>
                </figure>
            {% endif %}
        {% endfor %}
    {% endif %}
{% endfor %}

I even tried to eager load the related members entries, but, to my surprise, that even added queries.

I must be on the wrong path. Could anyone enlighten me?

Thanks

Update to my question: I know how to eager load the related entries:

{% set member = craft.entries({
        section: 'member',
        relatedTo: entry,
        with: [
            ['cf_ma_asset', { withTransforms: [{ width: 30, height: 30 }] }],
        ]
    })
%}

To my surprise that adds queries, but is much faster...

Answer 1

There’s a lot going on here. Let’s first work out the outline of your algorithm, and therefore where things could be improved.

• Query and iterate memberships
  • Query and iterate members
    • Query first asset
      • Get asset transform

Note: Every time a transform is used, Craft first has to check whether the transform has been generated (from the docs)

As you can see, there are multiple levels of nested relations going on. You’ve mentioned using eager-loading already, but it is definitely beneficial here. One of the cool things Craft can do is eager-load nested relations.

Have you tried something like this?

{% set memberships = craft.entries({
    section: 'membership',
    order: 'cf_membershipShorthand',
    with: [
        '{relatedMembersField}.cf_ma_asset'
    ]
}) %}

{% for membership in memberships %}
    {% for member in membership.{relatedMembersField} %}
        {% set image = member.cf_ma_asset[0] ?? null %}
        {% set imageParams = { width: 30, height: 30 } %}

        {% if image %}
            {# Output image with transform #}
        {% endif %}
    {% endfor %}
{% endfor %}

Where {relatedMembersField} is the handle of your entries field.