Order by reverse relationship count It feels like I’m trying to do the impossible, but I can’t imagine this problem is unique to my project. Hoping someone here has figured it out before!
I have two channels, creators and products, and I can assign any number of products to a creator through a related entries field: creatorProducts.
How do I display the top 10 most popular products? Or, in other words, how do I sort the products by the number of creators they’ve been assigned to?
Haven’t been able to find the answer anywhere. Welp.
If you don't have tons and tons of creators, you could do that by querying creators and re-arranging the results:
{# Get all creators and eager-load the products #}
{% set creators = craft.entries()
    .section('creators')
    .with('products')
    .all()
%}

{# Declare an array to store all related product ID #}
{% set products = [] %}

{# Build an array of all product ID occurences #}
{% for item in creators %}
    {% for product in item.products %}
        {% set products = products | merge([product.id]) %}
    {% endfor %}
{% endfor %}

{# Group by product ID #}
{% set groupedProducts = products | group(v =&gt; v) %}

{# Sort by occurence count #}
{% set sortedGroupedProducts = groupedProducts | sort((a, b) =&gt; b &lt;=&gt; a)  %}

{# Now you can get: Product ID | Occurences #}
{% for c in sortedGroupedProducts | slice(0,10)%}
    Product ID: {{ c[0] }} | Occurences: {{ c|length }}&lt;br&gt;
{% endfor %}
&lt;hr&gt;
{# To fetch the products, extract the keys from the above and flatten to a comma seperated list of product IDs #}
{% set productIds = sortedGroupedProducts|keys|join(',') %}

{# Query #}
{% set products = craft.entries()
    .section('products')
    .id(productIds)
    .fixedOrder()
    .limit(10)
    .all() %}

{% for item in products %}
    {{ item.title }} ({{ sortedGroupedProducts[item.id] | length }})&lt;br&gt;
{% endfor %}

This could probably be improved or even moved to PHP but if you have to do in Twig, it should work.