1

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

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.

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() %}
  • See updated question. – nitech Dec 2 at 9:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.