0

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.enter image description here

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:

<tbody class="bg-white divide-y divide-gray-200 text-sm">
      {% 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 %}
          <tr class="hover:bg-gray-50">
            <td class="px-6 py-3 whitespace-nowrap">
              {{ entry.tuitionCourse[0] }}
            </td>
            <td class="px-6 py-3 whitespace-nowrap">
              {{ entry.weekday }}
            </td>
            <td class="px-6 py-3 whitespace-nowrap">
              <div class="flex items-center">
                {{ svg('@icons/bx/line/bx-time.svg')|attr({class: "w-4 h-4 mr-1.5 shrink-0 fill-current"}) }}
                <div>
                  {{ macro.timeFormatted(entry.startTime) }} to {{ macro.timeFormatted(entry.endTime) }}
                </div>
              </div>
            </td>
            <td class="px-6 py-3 whitespace-nowrap text-center">
              {{ subscribed.count }}
            </td>
            <td class="px-6 py-3 whitespace-nowrap text-center">
              {{ spacesAvailable }}
            </td>
            <td class="px-3 py-3 whitespace-nowrap">
              <a href="{{ entry.url }}" target="_blank" rel="noopener noreferrer" class="text-pink-600 hover:text-gray-900">
                View Class List
              </a>
            </td>
            <td class="px-3 py-3 whitespace-nowrap">
              <a href="{{ entry.cpEditUrl }}" target="_blank" rel="noopener noreferrer" class="text-pink-600 hover:text-gray-900 inline-flex items-center">
                {{ svg('@icons/bx/solid/bxs-edit.svg')|attr({ class:"w-4 h-4 shrink-0 fill-current mr-1" }) }}
                Edit Lesson
              </a>
            </td>
          </tr>
        {% endif %}
      {% endfor %}
      </tbody>

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.

1 Answer 1

2

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.

1
  • Thanks for this @bencrocker. Much appreciated. May 19 at 8:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.