Answers.Body: <p>Even without <em>Products of this type have multiple variants</em> enabled on your product type, a product still has one variant so doing the below should work.</p>
  
  <pre><code>{% set products = craft.variants().hasStock().all() %}
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-12-02T09:11:08.577Z
Answers.Text: See updated question.
Answers.UserDisplayName: nitech
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-11-29T18:46:14.710Z
Answers.LastActivityDate: 2019-11-29T18:46:14.710Z
Answers.UserDisplayName: Oli
Body: <p>In Craft Commerce 2, how can I query products that are in stock? These are products without variants. </p>
  
  <h3>Edit: Perhaps this is a Craft-bug?</h3>
  
  <p>This particular product type used to support variants - but I removed that support prior to importing all the products with Feed Me. </p>
  
  <p>When I look at the <code>commerce_producttypes</code>-table, I see some remnants of data in the <code>variantFieldLayoutId</code> suggesting that the product type supports variants: </p>
  
  <p><a href="https://i.sstatic.net/Bv5uw.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/Bv5uw.png" alt="variantFieldLayoutId seems to suggest that product type still supports variants"></a></p>
  
  <p>When I run queries such as:</p>
  
  <p><code>{% set children = craft.products.hasVariant({ hasStock: true }).limit(50) %}</code></p>
  
  <p>I actually only get products from the three other product types (that has never supported variants).</p>
  
  <p>But when I run queries such as: </p>
  
  <p><code>{% set children = craft.products({ type: 'threads' }).productImage(":notempty:").limit(20) %}</code></p>
  
  <p>I do get products from the product type "threads". Strange eh? </p>
  
  <p>I tried setting the <code>variantFieldLayoutId</code> field to <code>Null</code>, but it didn't yield any difference. </p>
  
  <p>I tried deleting the product type, re-adding it and re-importing all the products, but still same result. </p>
  
  <h3>Something weird with variants</h3>
  
  <p>When I run the following query: </p>
  
  <p><code>craft.products({ type: 'threads' }).productImage(":notempty:").hasVariant({ hasStock: true }).limit(20)</code></p>
  
  <p>I get no results. It produces the following query: </p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT -- loads of fields
  FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId`
  FROM `elements` `elements`
  INNER JOIN `commerce_products` `commerce_products` ON `commerce_products`.`id` = `elements`.`id`
  INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
  INNER JOIN `content` `content` ON (`content`.`elementId` = `elements`.`id`) AND (`content`.`siteId` = `elements_sites`.`siteId`)
  WHERE (`commerce_products`.`typeId`='6') AND (`commerce_products`.`id` IN ('16458', '16477', '16491')) AND (`elements_sites`.`siteId`='1') AND ((select count(`relations_productImage`.`id`) from `relations` `relations_productImage` where `relations_productImage`.`sourceId` = `elements`.`id` and `relations_productImage`.`fieldId` = '94') != 0) AND (`elements`.`archived`=FALSE) AND (((`elements`.`enabled`=TRUE) AND (`elements_sites`.`enabled`=TRUE)) AND (`commerce_products`.`postDate` &lt;= '2019-12-02 10:31:14') AND ((`commerce_products`.`expiryDate` IS NULL) OR (`commerce_products`.`expiryDate` &gt; '2019-12-02 10:31:14'))) AND (`elements`.`dateDeleted` IS NULL) AND (`elements_sites`.`enabled`=TRUE) AND (`elements`.`draftId` IS NULL) AND (`elements`.`revisionId` IS NULL)
  ORDER BY `commerce_products`.`postDate` DESC
  LIMIT 20) `subquery`
  INNER JOIN `commerce_products` `commerce_products` ON `commerce_products`.`id` = `subquery`.`elementsId`
  INNER JOIN `elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
  INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`elementsSitesId`
  INNER JOIN `content` `content` ON `content`.`id` = `subquery`.`contentId`
  ORDER BY `commerce_products`.`postDate` DESC
  </code></pre>
  
  <p>Notice the <code>AND ('commerce_products'.'id' IN ('16458', '16477', '16491'))</code></p>
  
  <p>When I remove it, I get the results I expect. The three IDs belong to the only three products that are not part of the product type 6 (former type 5 in the screenshot).</p>
  
  <h3>More hasVariant subquery weirdness</h3>
  
  <p>When I run this query: </p>
  
  <pre><code>{% set children = craft.products({
      type: 'threads'
  }).hasVariant({
      stock: '&gt; 1',
  }) %}
  </code></pre>
  
  <p>Which creates this SQL:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT 
  
  -- loads of fields
  
  FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId`
  FROM `elements` `elements`
  INNER JOIN `commerce_products` `commerce_products` ON `commerce_products`.`id` = `elements`.`id`
  INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
  INNER JOIN `content` `content` ON (`content`.`elementId` = `elements`.`id`) AND (`content`.`siteId` = `elements_sites`.`siteId`)
  WHERE (`commerce_products`.`typeId`='6') AND (`commerce_products`.`id` IN 
  ('20396', '20447', '20495', 
  
  -- plus all other ids that belong to products with variants that have a stock greater than 0
  
  )) AND (`elements_sites`.`siteId`='1') AND (`elements`.`archived`=FALSE) AND (((`elements`.`enabled`=TRUE) AND (`elements_sites`.`enabled`=TRUE)) AND (`commerce_products`.`postDate` &lt;= '2019-12-02 11:02:06') AND ((`commerce_products`.`expiryDate` IS NULL) OR (`commerce_products`.`expiryDate` &gt; '2019-12-02 11:02:06'))) AND (`elements`.`dateDeleted` IS NULL) AND (`elements_sites`.`enabled`=TRUE) AND (`elements`.`draftId` IS NULL) AND (`elements`.`revisionId` IS NULL)
  ORDER BY `commerce_products`.`postDate` DESC) `subquery`
  INNER JOIN `commerce_products` `commerce_products` ON `commerce_products`.`id` = `subquery`.`elementsId`
  INNER JOIN `elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
  INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`elementsSitesId`
  INNER JOIN `content` `content` ON `content`.`id` = `subquery`.`contentId`
  ORDER BY `commerce_products`.`postDate` DESC
  </code></pre>
  
  <p>I actually get the desired result: All products (variants) that have stock > 0. </p>
  
Tags: <plugin-craftcommerce2><ecommerce>
Title: Get products that are in stock
ThreadText: # Get products that are in stock
  
  (Asked by: username_0 on 2019-11-29)
  
  In Craft Commerce 2, how can I query products that are in stock? These are products without variants.
  
  ### Edit: Perhaps this is a Craft-bug?
  
  This particular product type used to support variants - but I removed that support prior to importing all the products with Feed Me.
  
  When I look at the `commerce_producttypes`-table, I see some remnants of data in the `variantFieldLayoutId` suggesting that the product type supports variants:
  
  [![variantFieldLayoutId seems to suggest that product type still supports variants](https://i.sstatic.net/Bv5uw.png)](https://i.sstatic.net/Bv5uw.png)
  
  When I run queries such as:
  
  `{% set children = craft.products.hasVariant({ hasStock: true }).limit(50) %}`
  
  I actually only get products from the three other product types (that has never supported variants).
  
  But when I run queries such as:
  
  `{% set children = craft.products({ type: 'threads' }).productImage(":notempty:").limit(20) %}`
  
  I do get products from the product type "threads". Strange eh?
  
  I tried setting the `variantFieldLayoutId` field to `Null`, but it didn't yield any difference.
  
  I tried deleting the product type, re-adding it and re-importing all the products, but still same result.
  
  ### Something weird with variants
  
  When I run the following query:
  
  `craft.products({ type: 'threads' }).productImage(":notempty:").hasVariant({ hasStock: true }).limit(20)`
  
  I get no results. It produces the following query:
  
  ```sql
  SELECT -- loads of fields
  FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId`
  FROM `elements` `elements`
  INNER JOIN `commerce_products` `commerce_products` ON `commerce_products`.`id` = `elements`.`id`
  INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
  INNER JOIN `content` `content` ON (`content`.`elementId` = `elements`.`id`) AND (`content`.`siteId` = `elements_sites`.`siteId`)
  WHERE (`commerce_products`.`typeId`='6') AND (`commerce_products`.`id` IN ('16458', '16477', '16491')) AND (`elements_sites`.`siteId`='1') AND ((select count(`relations_productImage`.`id`) from `relations` `relations_productImage` where `relations_productImage`.`sourceId` = `elements`.`id` and `relations_productImage`.`fieldId` = '94') != 0) AND (`elements`.`archived`=FALSE) AND (((`elements`.`enabled`=TRUE) AND (`elements_sites`.`enabled`=TRUE)) AND (`commerce_products`.`postDate` <= '2019-12-02 10:31:14') AND ((`commerce_products`.`expiryDate` IS NULL) OR (`commerce_products`.`expiryDate` > '2019-12-02 10:31:14'))) AND (`elements`.`dateDeleted` IS NULL) AND (`elements_sites`.`enabled`=TRUE) AND (`elements`.`draftId` IS NULL) AND (`elements`.`revisionId` IS NULL)
  ORDER BY `commerce_products`.`postDate` DESC
  LIMIT 20) `subquery`
  INNER JOIN `commerce_products` `commerce_products` ON `commerce_products`.`id` = `subquery`.`elementsId`
  INNER JOIN `elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
  INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`elementsSitesId`
  INNER JOIN `content` `content` ON `content`.`id` = `subquery`.`contentId`
  ORDER BY `commerce_products`.`postDate` DESC
  
  ```
  
  Notice the `AND ('commerce_products'.'id' IN ('16458', '16477', '16491'))`
  
  When I remove it, I get the results I expect. The three IDs belong to the only three products that are not part of the product type 6 (former type 5 in the screenshot).
  
  ### More hasVariant subquery weirdness
  
  When I run this query:
  
  ```
  {% set children = craft.products({
      type: 'threads'
  }).hasVariant({
      stock: '> 1',
  }) %}
  
  ```
  
  Which creates this SQL:
  
  ```sql
  SELECT 
  
  -- loads of fields
  
  FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId`
  FROM `elements` `elements`
  INNER JOIN `commerce_products` `commerce_products` ON `commerce_products`.`id` = `elements`.`id`
  INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
  INNER JOIN `content` `content` ON (`content`.`elementId` = `elements`.`id`) AND (`content`.`siteId` = `elements_sites`.`siteId`)
  WHERE (`commerce_products`.`typeId`='6') AND (`commerce_products`.`id` IN 
  ('20396', '20447', '20495', 
  
  -- plus all other ids that belong to products with variants that have a stock greater than 0
  
  )) AND (`elements_sites`.`siteId`='1') AND (`elements`.`archived`=FALSE) AND (((`elements`.`enabled`=TRUE) AND (`elements_sites`.`enabled`=TRUE)) AND (`commerce_products`.`postDate` <= '2019-12-02 11:02:06') AND ((`commerce_products`.`expiryDate` IS NULL) OR (`commerce_products`.`expiryDate` > '2019-12-02 11:02:06'))) AND (`elements`.`dateDeleted` IS NULL) AND (`elements_sites`.`enabled`=TRUE) AND (`elements`.`draftId` IS NULL) AND (`elements`.`revisionId` IS NULL)
  ORDER BY `commerce_products`.`postDate` DESC) `subquery`
  INNER JOIN `commerce_products` `commerce_products` ON `commerce_products`.`id` = `subquery`.`elementsId`
  INNER JOIN `elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
  INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`elementsSitesId`
  INNER JOIN `content` `content` ON `content`.`id` = `subquery`.`contentId`
  ORDER BY `commerce_products`.`postDate` DESC
  
  ```
  
  I actually get the desired result: All products (variants) that have stock > 0.
  
  **Tags:** plugin-craftcommerce2, ecommerce
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2019-11-29. Score: 1
  
  Even without *Products of this type have multiple variants* enabled on your product type, a product still has one variant so doing the below should work.
  
  ```
  {% set products = craft.variants().hasStock().all() %}
  
  ```
  
  > **Comments:**
  > **username_0**: See updated question.  
  
  ---
  
  