<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>

CC BY-SA 4.0
2019-12-02T09:11:08.577Z
See updated question.
nitech
CC BY-SA 4.0
2019-11-29T18:46:14.710Z
2019-11-29T18:46:14.710Z
Oli
<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>

<plugin-craftcommerce2><ecommerce>
Get products that are in stock
# 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: 

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: 
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:
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.  

---

