<p>I already responded to your question in a GitHub issue, but here we go again.</p>
<p>Once you execute the <code>tuitionLessonsQuery</code> query, you can sort the resulting entries using the <a href="https://craftcms.com/docs/3.x/dev/filters.html#multisort" rel="nofollow noreferrer">multisort</a> filter, before looping over them.</p>
<pre><code>{% set entries = tuitionLessonsQuery.all() %}

{% set sortedEntries = entries|multisort(entry =&gt; craft.entries
    .section('children')
    .relatedTo({
        targetElement: entry,
        field: 'tuitionLesson',
    })
    .count()
) %}

{% for entry in sortedEntries %}
    ...
{% endfor %}
</code></pre>
<p>This will sort by the number of subscribed children in ascending order, which is equivalent to sorting by the number of available spaces in descending order.</p>
<p>It's not very performant, since it executes a database query for every entry, resulting in <em>N+1</em> queries, but if you use pagination and have an upper limit per page (say 50), then it should work just fine.</p>
<p>The alternative is to use <code>innerJoin</code> statements on the element query, which can quickly get confusing, but is possible. If performance is a high priority though, then I'd opt for performing the logic in a custom module.</p>

CC BY-SA 4.0
2022-05-19T08:50:02.677Z
Thanks for this @bencrocker. Much appreciated.
Terry Upton
CC BY-SA 4.0
2022-05-18T16:36:51.787Z
2022-05-18T17:05:32.313Z
2022-05-18T17:05:32.313Z
Ben Croker
<p>I am working on a sprig board that filters out Lessons from a structure in Craft.<br />
This screenshot better illustrates this and how it works.<a href="https://i.sstatic.net/Lk5jI.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/Lk5jI.png" alt="enter image description here" /></a></p>
<p>The main table is built using the following:</p>
<pre><code>{# Sets default values. #}
{% set search = search ?? '' %}
{% set section = section ?? 'tuitionLessons' %}
{% set status = status ?? 'live' %}
{% set tuitionCourse = tuitionCourse ?? '' %}
{% set limit = limit ?? 50 %}
{% set page = page ?? 1 %}
{% set orderBy = orderBy ?? 'title' %}
{% set sortBy = sortBy ?? 'asc' %}
{% set showSpacesOnly = showSpacesOnly ?? 1 %}

{# Create an array of filters. #}
{% set filters = {
  search: search,
  section: section,
  tuitionCourse: tuitionCourse
} %}

{# Push the URL into the history stack with the filters, adding extra values. #}
{% do sprig.pushUrl('?' ~ filters|merge({
  page: page,
  orderBy: orderBy,
  sortBy: sortBy,
})|url_encode) %}

{# Generate the entry query using the filters, adding extra conditions. #}
{% set tuitionLessonsQuery = craft.entries(filters|merge({
  orderBy: orderBy ~ ' ' ~ sortBy,
  with: [
    'location',
    'tuitionCourse'
  ]
})) %}
</code></pre>
<p>Within this I then have two columns that I am populating and want to sort against (DESC/ASC).<br />
'Subscribed' and 'Spaces Available'</p>
<p>These columns are populated by checking how many Children (another channel in Craft) that are related to each lesson.  However, this currently takes place inside the for loop for the lessons.</p>
<p>The code looks like this:</p>
<pre><code>&lt;tbody class=&quot;bg-white divide-y divide-gray-200 text-sm&quot;&gt;
      {% for entry in tuitionLessonsQuery.all() %}
        {% set subscribed = craft.entries
          .section('children')
          .with('tuitionLesson')
          .relatedTo({
            targetElement:entry,
            field: 'tuitionLesson'
          }) %}
        
        {% set spacesAvailable = (12 - subscribed.count) %}
        
        {% if spacesAvailable &gt; 0 or showSpacesOnly == 0 %}
          &lt;tr class=&quot;hover:bg-gray-50&quot;&gt;
            &lt;td class=&quot;px-6 py-3 whitespace-nowrap&quot;&gt;
              {{ entry.tuitionCourse[0] }}
            &lt;/td&gt;
            &lt;td class=&quot;px-6 py-3 whitespace-nowrap&quot;&gt;
              {{ entry.weekday }}
            &lt;/td&gt;
            &lt;td class=&quot;px-6 py-3 whitespace-nowrap&quot;&gt;
              &lt;div class=&quot;flex items-center&quot;&gt;
                {{ svg('@icons/bx/line/bx-time.svg')|attr({class: &quot;w-4 h-4 mr-1.5 shrink-0 fill-current&quot;}) }}
                &lt;div&gt;
                  {{ macro.timeFormatted(entry.startTime) }} to {{ macro.timeFormatted(entry.endTime) }}
                &lt;/div&gt;
              &lt;/div&gt;
            &lt;/td&gt;
            &lt;td class=&quot;px-6 py-3 whitespace-nowrap text-center&quot;&gt;
              {{ subscribed.count }}
            &lt;/td&gt;
            &lt;td class=&quot;px-6 py-3 whitespace-nowrap text-center&quot;&gt;
              {{ spacesAvailable }}
            &lt;/td&gt;
            &lt;td class=&quot;px-3 py-3 whitespace-nowrap&quot;&gt;
              &lt;a href=&quot;{{ entry.url }}&quot; target=&quot;_blank&quot; rel=&quot;noopener noreferrer&quot; class=&quot;text-pink-600 hover:text-gray-900&quot;&gt;
                View Class List
              &lt;/a&gt;
            &lt;/td&gt;
            &lt;td class=&quot;px-3 py-3 whitespace-nowrap&quot;&gt;
              &lt;a href=&quot;{{ entry.cpEditUrl }}&quot; target=&quot;_blank&quot; rel=&quot;noopener noreferrer&quot; class=&quot;text-pink-600 hover:text-gray-900 inline-flex items-center&quot;&gt;
                {{ svg('@icons/bx/solid/bxs-edit.svg')|attr({ class:&quot;w-4 h-4 shrink-0 fill-current mr-1&quot; }) }}
                Edit Lesson
              &lt;/a&gt;
            &lt;/td&gt;
          &lt;/tr&gt;
        {% endif %}
      {% endfor %}
      &lt;/tbody&gt;
</code></pre>
<p>This sits within the Lessons for loop <code>{% for entry in tuitionLessonsQuery.all() %}</code>
This works fine and populates the correct values. However, as this is related data and content and sites within the initial element query, I cannot use Sprig to allow these columns to be sorted (ASC/DESC) like I could with Year and Day of the week etc, which are fields directly on the main tuitionLessonsQuery.</p>
<p>I looked into Preparse plugin, but this falls over in that it would only populate these fields upon saving the Lesson and this happens first, then children are later assigned to the lesson over a period of time. So it pretty much rules this out.</p>
<p>I have also looked at the multisort filter <a href="https://craftcms.com/docs/3.x/dev/filters.html#multisort" rel="nofollow noreferrer">https://craftcms.com/docs/3.x/dev/filters.html#multisort</a>. However, I cannot see how this would work when the data I want to sort by exists/is constructed within the element query.</p>
<p>Is this at all possible? Any ideas on how to approach this?
Should I be merging the data somehow?</p>
<p>I am not looking for sprig support here, as I can make that work, once I have the element query sorting by the correct data. I am looking for support on how to construct these queries and data to be able to sort by these related counts.</p>
<p>Thanks.</p>

<relatedto><elementquery><plugin-sprig><multisort>
Sort entries by separate related entries count
# Sort entries by separate related entries count

(Asked by: username_0 on 2022-05-18)

I am working on a sprig board that filters out Lessons from a structure in Craft.
This screenshot better illustrates this and how it works.
The main table is built using the following:
{# Sets default values. #}
{% set search = search ?? '' %}
{% set section = section ?? 'tuitionLessons' %}
{% set status = status ?? 'live' %}
{% set tuitionCourse = tuitionCourse ?? '' %}
{% set limit = limit ?? 50 %}
{% set page = page ?? 1 %}
{% set orderBy = orderBy ?? 'title' %}
{% set sortBy = sortBy ?? 'asc' %}
{% set showSpacesOnly = showSpacesOnly ?? 1 %}

{# Create an array of filters. #}
{% set filters = {
  search: search,
  section: section,
  tuitionCourse: tuitionCourse
} %}

{# Push the URL into the history stack with the filters, adding extra values. #}
{% do sprig.pushUrl('?' ~ filters|merge({
  page: page,
  orderBy: orderBy,
  sortBy: sortBy,
})|url_encode) %}

{# Generate the entry query using the filters, adding extra conditions. #}
{% set tuitionLessonsQuery = craft.entries(filters|merge({
  orderBy: orderBy ~ ' ' ~ sortBy,
  with: [
    'location',
    'tuitionCourse'
  ]
})) %}

Within this I then have two columns that I am populating and want to sort against (DESC/ASC).
'Subscribed' and 'Spaces Available'
These columns are populated by checking how many Children (another channel in Craft) that are related to each lesson.  However, this currently takes place inside the for loop for the lessons.
The code looks like this:

      {% for entry in tuitionLessonsQuery.all() %}
        {% set subscribed = craft.entries
          .section('children')
          .with('tuitionLesson')
          .relatedTo({
            targetElement:entry,
            field: 'tuitionLesson'
          }) %}
        
        {% set spacesAvailable = (12 - subscribed.count) %}
        
        {% if spacesAvailable > 0 or showSpacesOnly == 0 %}
          
            
              {{ entry.tuitionCourse[0] }}
            
            
              {{ entry.weekday }}
            
            
              
                {{ svg('@icons/bx/line/bx-time.svg')|attr({class: "w-4 h-4 mr-1.5 shrink-0 fill-current"}) }}
                
                  {{ macro.timeFormatted(entry.startTime) }} to {{ macro.timeFormatted(entry.endTime) }}
                
              
            
            
              {{ subscribed.count }}
            
            
              {{ spacesAvailable }}
            
            
              
                View Class List
              
            
            
              
                {{ svg('@icons/bx/solid/bxs-edit.svg')|attr({ class:"w-4 h-4 shrink-0 fill-current mr-1" }) }}
                Edit Lesson
              
            
          
        {% endif %}
      {% endfor %}
      

This sits within the Lessons for loop {% for entry in tuitionLessonsQuery.all() %}
This works fine and populates the correct values. However, as this is related data and content and sites within the initial element query, I cannot use Sprig to allow these columns to be sorted (ASC/DESC) like I could with Year and Day of the week etc, which are fields directly on the main tuitionLessonsQuery.
I looked into Preparse plugin, but this falls over in that it would only populate these fields upon saving the Lesson and this happens first, then children are later assigned to the lesson over a period of time. So it pretty much rules this out.
I have also looked at the multisort filter https://craftcms.com/docs/3.x/dev/filters.html#multisort. However, I cannot see how this would work when the data I want to sort by exists/is constructed within the element query.
Is this at all possible? Any ideas on how to approach this?
Should I be merging the data somehow?
I am not looking for sprig support here, as I can make that work, once I have the element query sorting by the correct data. I am looking for support on how to construct these queries and data to be able to sort by these related counts.
Thanks.

**Tags:** relatedto, elementquery, plugin-sprig, multisort


---

## 1 answers

### Answer by username_1 on 2022-05-18. Score: 2 | Accepted answer

I already responded to your question in a GitHub issue, but here we go again.
Once you execute the tuitionLessonsQuery query, you can sort the resulting entries using the multisort filter, before looping over them.
{% set entries = tuitionLessonsQuery.all() %}

{% set sortedEntries = entries|multisort(entry => craft.entries
    .section('children')
    .relatedTo({
        targetElement: entry,
        field: 'tuitionLesson',
    })
    .count()
) %}

{% for entry in sortedEntries %}
    ...
{% endfor %}

This will sort by the number of subscribed children in ascending order, which is equivalent to sorting by the number of available spaces in descending order.
It's not very performant, since it executes a database query for every entry, resulting in N+1 queries, but if you use pagination and have an upper limit per page (say 50), then it should work just fine.
The alternative is to use innerJoin statements on the element query, which can quickly get confusing, but is possible. If performance is a high priority though, then I'd opt for performing the logic in a custom module.

> **Comments:**
> **username_0**: Thanks for this @bencrocker. Much appreciated.  

---

