<p>Tweak your code a bit. Instead of using <code>hasProduct</code>, which can jumble up the order, call <code>variants</code> on each product as you loop through:</p>
<pre><code>// grab current category to filter products
{% set currentCategory = (activeNode and activeNode.getTypeLabel() == 'Categoria') ? activeNode.element : null %}

{# loop through each product #}
{% for p in craft.products.type('whateverProductType').relatedTo(currentCategory).all %}

  {% for v in p.variants().sortBy([['featuredProduct','desc'],['postDate','desc']]).collect() %}

   {# do whatever #}
   {{ v.description }}&lt;br&gt;

  {% endfor %}

{% endfor %}
</code></pre>

CC BY-SA 4.0
2024-12-13T13:19:54.207Z
Thanks, I'm gonna experiment on your suggestion and keep you posted.
Masiorama
CC BY-SA 4.0
2024-12-19T10:19:56.450Z
I checked your suggestion and indeed hasProduct is messing up the order, but it's kinda hard to adopt the product loop solution in my case since I need to keep it as a variantQuery rather than a collection of objects. I need to filter, search, limit, add where conditions and add relations data (_with_) later in the code ... I oversimplified the question maybe. I'll keep investigating and keep you posted, thanks.
Masiorama
CC BY-SA 4.0
2024-12-19T10:33:01.687Z
Just a thought: do you think that https://craftcms.com/docs/commerce/5.x/system/products-variants.html#variant-hasproduct  could be somehow seen as  faulty for sorting purpouses as being targeted to "Narrows the query results to only variants for certain products." but can't keep default sorting? I could open an issue, just in case.
Masiorama
CC BY-SA 4.0
2024-12-21T10:13:49.060Z
FWIW sorting can get complex quickly. You may want to look at something like [SuperSort](https://plugins.craftcms.com/supersort) if the built-in Craft methods can’t do what you’re after.
RitterKnight
CC BY-SA 4.0
2024-12-21T17:16:08.203Z
Thanks, I will dig into it.
Masiorama
CC BY-SA 4.0
2024-12-12T19:44:49.240Z
2024-12-12T19:44:49.240Z
RitterKnight
<p>With Commerce 5.2 it is possible to change product type to structures, which was done in order to manually sort products (<a href="https://github.com/craftcms/commerce/discussions/2302" rel="nofollow noreferrer">https://github.com/craftcms/commerce/discussions/2302</a>).</p>
<p>I changed my product types to structures inside the panel.</p>
<p>In my twig template I have:</p>
<pre><code>// grab current category to filter products
{% set currentCategory = (activeNode and activeNode.getTypeLabel() == 'Categoria') ? activeNode.element : null %}
{% set relatedProducts = craft.products().relatedTo(currentCategory) %}

{% set allVariants = craft.variants.hasProduct(relatedProducts) %}

{% set results = allVariants
        // limit, search, relatedTo and with not relevant stuff
    .orderBy('featuredProduct DESC, postDate DESC, sortOrder ASC, score')
%}

</code></pre>
<p>Note that I do not list products but variants, and I want to sort first by products sorting and then list ordered variants.</p>
<p>But if I change the <code>orderBy</code> params with something like <code>lft</code> (here <a href="https://craftcms.com/docs/5.x/reference/element-types/categories.html#example" rel="nofollow noreferrer">docs</a>):</p>
<p><code>.orderBy('featuredProduct DESC, lft, postDate DESC, sortOrder ASC, score')</code></p>
<p>the query is broken because there is no join between <code>structureelements</code> (which holds the <code>lft</code> field) and variants tables.</p>
<pre><code>SQLSTATE[42S22]: Column not found: 1054 Unknown column 'lft' in 'order clause'
</code></pre>
<p>Thanks for any suggestion.</p>

CC BY-SA 4.0
2024-11-26T15:04:06.963Z
Add missing twig code.
Masiorama
<plugin-craftcommerce><craft5>
How to query variants for structured products (commerce 5.2) to sort products
# How to query variants for structured products (commerce 5.2) to sort products

(Asked by: username_0 on 2024-11-26)

With Commerce 5.2 it is possible to change product type to structures, which was done in order to manually sort products (https://github.com/craftcms/commerce/discussions/2302).
I changed my product types to structures inside the panel.
In my twig template I have:
// grab current category to filter products
{% set currentCategory = (activeNode and activeNode.getTypeLabel() == 'Categoria') ? activeNode.element : null %}
{% set relatedProducts = craft.products().relatedTo(currentCategory) %}

{% set allVariants = craft.variants.hasProduct(relatedProducts) %}

{% set results = allVariants
        // limit, search, relatedTo and with not relevant stuff
    .orderBy('featuredProduct DESC, postDate DESC, sortOrder ASC, score')
%}


Note that I do not list products but variants, and I want to sort first by products sorting and then list ordered variants.
But if I change the orderBy params with something like lft (here docs):
.orderBy('featuredProduct DESC, lft, postDate DESC, sortOrder ASC, score')
the query is broken because there is no join between structureelements (which holds the lft field) and variants tables.
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'lft' in 'order clause'

Thanks for any suggestion.

**Tags:** plugin-craftcommerce, craft5

> **Comments:**
> **username_0**: Add missing twig code.  

---

## 1 answers

### Answer by username_1 on 2024-12-12. Score: 0

Tweak your code a bit. Instead of using hasProduct, which can jumble up the order, call variants on each product as you loop through:
// grab current category to filter products
{% set currentCategory = (activeNode and activeNode.getTypeLabel() == 'Categoria') ? activeNode.element : null %}

{# loop through each product #}
{% for p in craft.products.type('whateverProductType').relatedTo(currentCategory).all %}

  {% for v in p.variants().sortBy([['featuredProduct','desc'],['postDate','desc']]).collect() %}

   {# do whatever #}
   {{ v.description }}

  {% endfor %}

{% endfor %}

> **Comments:**
> **username_0**: Thanks, I'm gonna experiment on your suggestion and keep you posted.  
> **username_0**: I checked your suggestion and indeed hasProduct is messing up the order, but it's kinda hard to adopt the product loop solution in my case since I need to keep it as a variantQuery rather than a collection of objects. I need to filter, search, limit, add where conditions and add relations data (_with_) later in the code ... I oversimplified the question maybe. I'll keep investigating and keep you posted, thanks.  
> **username_0**: Just a thought: do you think that https://craftcms.com/docs/commerce/5.x/system/products-variants.html#variant-hasproduct  could be somehow seen as  faulty for sorting purpouses as being targeted to "Narrows the query results to only variants for certain products." but can't keep default sorting? I could open an issue, just in case.  
> **username_1**: FWIW sorting can get complex quickly. You may want to look at something like [SuperSort](https://plugins.craftcms.com/supersort) if the built-in Craft methods can’t do what you’re after.  
> **username_0**: Thanks, I will dig into it.  

---

