Multiple category relationship criteria (or)and(or)

Question

Is there a way to do relatedTo categories where the category is (a or b) and (c or d or e)?

I can get (a or b) working using:

{% paginate craft.commerce.products.relatedTo(categories).limit(20) as pageInfo, products %}

I can separately get (a and b) working using:

{% set relationParam = ['and', { sourceElement: product }] %}
{% for category in categories %}
    {% set relationParam = relationParam|merge([{ targetElement: category }]) %}
{% endfor %}
{% paginate craft.commerce.products.relatedTo(relationParam).limit(20) as pageInfo, products %}

I need to combine these though as I am working with 3 category groups for filtering and want to match products where it is related to any of the selected categories in group 1, and any of the selected categories in group 2, and any of the selected categories in group 3.

An example of selections would be like so:

Group 1: Essentials, Pearlised

Group 2: C6

Group 3: Reds, Greens

Using the 'or' syntax gives me too many results where they no longer match on each group and using the 'and' syntax is too restrictive and wants the product to have all of the selected categories. Does anybody know how I can nest the relatedTo criteria in such a way to get what I need?

I've attempted using the following but it didn't yield any results at all:

{% set relationParam = ['and'] %}
{% if rangeCategories|length %}
    {% set rangeParam = ['or'] %}
    {% for category in rangeCategories %}
        {% set rangeParam = rangeParam|merge([{ targetElement: category }]) %}
    {% endfor %}
    {% set relationParam = relationParam|merge(rangeParam) %}
{% endif %}
{% if sizeCategories|length %}
    {% set sizeParam = ['or'] %}
    {% for category in sizeCategories %}
        {% set sizeParam = sizeParam|merge([{ targetElement: category }]) %}
    {% endfor %}
    {% set relationParam = relationParam|merge(sizeParam) %}
{% endif %}
{% if colourCategories|length %}
    {% set colourParam = ['or'] %}
    {% for category in colourCategories %}
        {% set colourParam = colourParam|merge([{ targetElement: category }]) %}
    {% endfor %}
    {% set relationParam = relationParam|merge(colourParam) %}
{% endif %}
{% paginate craft.commerce.products.relatedTo(relationParam).limit(20) as pageInfo, products %}

I've not seen any examples of people using both 'and' 'or' syntax and there are no notes about this on the documentation so any help would be appreciated. Thank you.

Answer 1

I think what you want here is the union of (products related to a or b) and (products related to c, d, or e).

One good way to do these more complicated queries is to construct a set of the ids, and then do your final query based on that.

So, we will get the ids of products related to a or b:

{% set aOrBProductIds = craft.commerce.products({
    relatedTo: aOrBCategories,
    limit: null
  }).ids()
%}

and the ids of products related to c, d, or e:

{% set cOrDorEProductIds = craft.commerce.products({
    relatedTo: cOrDorECategories),
    limit: null
  }).ids()
%}

Now merge those arrays into one array with all of the ids:

{% set productIds = aOrBProductIds | merge(cOrDorEProductIds) %}

Finally, get the products, and paginate:

{% set productsToPaginate = craft.commerce.products.id(productIds) %}
{% paginate productsToPaginate.limit(20) as pageInfo, products %}

Answer 2

After Marion Newlevant suggested the merging of arrays of productIds instead of doing complex relatedTo parameters, I modified my code to read as follows:

{% if rangeCategories|length or sizeCategories|length or colourCategories|length %}
    {% set productIds = [] %}
    {% set rangeProductIds = "" %}
    {% set sizeProductIds = "" %}
    {% set colourProductIds = "" %}
    {% if rangeCategories|length %}
        {% set rangeProductIds = craft.commerce.products.type('envelopes').relatedTo(rangeCategories).limit(null).ids() %}
        {% set productIds = productIds|merge(rangeProductIds) %}
    {% endif %}
    {% if sizeCategories|length %}
        {% set sizeProductIds = craft.commerce.products.type('envelopes').relatedTo(sizeCategories).limit(null).ids() %}
        {% set productIds = productIds|merge(sizeProductIds) %}
    {% endif %}
    {% if colourCategories|length %}
        {% set colourProductIds = craft.commerce.products.type('envelopes').relatedTo(colourCategories).limit(null).ids() %}
        {% set productIds = productIds|merge(colourProductIds) %}
    {% endif %}
    {% set finalIds = [] %}
    {% for productId in productIds %}
        {% set add = "true" %}
        {% if rangeProductIds|length %}
            {% if productId not in rangeProductIds %}
                {% set add = "false" %}
            {% endif %}
        {% endif %}
        {% if sizeProductIds|length %}
            {% if productId not in sizeProductIds %}
                {% set add = "false" %}
            {% endif %}
        {% endif %}
        {% if colourProductIds|length %}
            {% if productId not in colourProductIds %}
                {% set add = "false" %}
            {% endif %}
        {% endif %}
        {% if add == "true" %}
            {% set finalIds = finalIds|merge([productId]) %}
        {% endif %}
    {% endfor %}
    {% paginate craft.commerce.products.id(finalIds).limit(20) as pageInfo, envelopes %}
{% else %}
    {% paginate craft.commerce.products.type('envelopes').limit(20) as pageInfo, envelopes %}
{% endif %}

This means that instead of merging and showing all products that belong to each one of the selected categories, we are now checking if the productId exists in all of the selected category group arrays. This has meant that the filtering now works for all combinations as follows:

[C5] [Reds or Greens]

[Pearlised] [Square or C6] [Golds]

[Pearlised or Essentials] [C5] [Blues or Greens or Reds]

[C5]

Thank you Marion for pointing me in the direction of array merging the ids with simple relatedTo parameters. If anyone else needs to do this kind of category querying then please feel free to use this code block.