Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-12-13T13:19:54.207Z
Answers.Text: Thanks, I'm gonna experiment on your suggestion and keep you posted.
Answers.UserDisplayName: Masiorama
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-12-19T10:19:56.450Z
Answers.Text: 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.
Answers.UserDisplayName: Masiorama
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-12-19T10:33:01.687Z
Answers.Text: 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.
Answers.UserDisplayName: Masiorama
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-12-21T10:13:49.060Z
Answers.Text: 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.
Answers.UserDisplayName: RitterKnight
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-12-21T17:16:08.203Z
Answers.Text: Thanks, I will dig into it.
Answers.UserDisplayName: Masiorama
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-12-12T19:44:49.240Z
Answers.LastActivityDate: 2024-12-12T19:44:49.240Z
Answers.UserDisplayName: RitterKnight
Body: <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>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2024-11-26T15:04:06.963Z
Comments.Text: Add missing twig code.
Comments.UserDisplayName: Masiorama
Tags: <plugin-craftcommerce><craft5>
Title: How to query variants for structured products (commerce 5.2) to sort products
ThreadText: # 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](https://craftcms.com/docs/5.x/reference/element-types/categories.html#example)):
  
  `.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 }}<br>
  
    {% 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.  
  
  ---
  
  