List Commerce products by category with a custom order

Question

I'm using Commerce for the first time (v3), and I'm trying to display entries sorted by category in a custom order.

I followed the Knowledge Base instructions for setting up a Commerce Products field, and it was working fine on its own, but when I try to add in categories, my custom order is ignored. This is the code I'm using:

{% set productIds = shopCustomization.orderedProducts.all() %}
{% set categories = craft.categories.relatedTo({ sourceElement: productIds }).group('services').level(2) %}
{% for category in categories %}
    <h2>{{ category.title }}</h2>
    {% set products = craft.products.relatedTo(category) %}
    {% for product in products %}
        ...product details...
    {% endfor %}
{% endfor %}

Is it possible to use the custom sort order within categories?

Answer 1

Using relatedTo doesn't mean your element query will based the order of the related element ids, in you case is the products id, so you have to re-sorting elements again or you can pass the order param in orderBy.

For this answer I will use the orderBy param for sorting it. Here is some kind of example :

{% set productIdsInString = productIds|join(',')%}
{% set orderBy = "FIELD(relations.sourceId, #{productIdsInString})" %}
{% set categories = craft.categories.relatedTo({ sourceElement: productIds }).group('services').level(2).innerJoin('{{%relations}} relations', '[[relations.targetId]] = [[elements.id]]').orderBy(expression(orderBy)).all() %}