Magento2: "Catalog Products List" includes "Out of Stock" products when "Quantity is In Stock" is a required condition of the widget and the products are given the quantity "Out of Stock" from within admin

[tomispepe]

Steps to reproduce

1. Install Magento version 2.0.0 or above.
2. Add the sample data
3. Create a new "Catalog Products List" widget
4. Add only 1 condition: "Quantity is In Stock"
5. Add this widget to the layout of cms homepage -> main content top
6. Save the Widget and Refresh the cache
7. Open the homepage and verify your list is displayed as expected
8. Note that this includes the "Joust Duffle Bag"
9. Go to admin -> products -> catalog -> and select the "Joust Duffle Bag" or some other product that _was_ included in the "Catalog Products List"

10. Set the quantity for "Joust Duffle Bag" to "Out of Stock"

    Expected result

11. Setting the quantity for "Joust Duffle Bag" to "Out of Stock" should update the catalog_product_entity_int table value for the "Joust Duffle Bag" and "quantity_and_stock_status" to match.

12. Setting the quantity for "Joust Duffle Bag" to "Out of Stock" should remove the "Joust Duffle Bag" from the "Catalog Products List" widget result when the "Quantity is In Stock" condition is specified where "all conditions are true"

    Actual result

13. Although the cataloginventory_stock_item and cataloginventory_stock_status tables are updated for the "Joust Duffle Bag", the "catalog_product_entity_int" table is never updated by changing the inventory to "Out of Stock"

[tomispepe]

For example if I create a widget with the condition stated and accept all other default settings I get the following mustache code:
{{widget type="Magento\CatalogWidget\Block\Product\ProductsList" products_count="10" template="product/widget/content/grid.phtml" conditions_encoded="a:2:[i:1;a:4:[s:4:type;s:50:Magento|CatalogWidget|Model|Rule|Condition|Combine;s:10:aggregator;s:3:all;s:5:value;s:1:1;s:9:new_child;s:0:``;]s:4:1--1;a:4:[s:4:type;s:50:Magento|CatalogWidget|Model|Rule|Condition|Product;s:9:attribute;s:25:quantity_and_stock_status;s:8:operator;s:2:!=;s:5:value;s:1:0;]]"}}

[tomispepe]

The widget type calls the following method:
\Magento\CatalogWidget\Block\Product\ProductsList::createCollection

By placing a breakpoint in this method on the last line before the method returns the collection of products you can run the following line:

$collection->getSelect()->assemble()

The SQL produced follows
SELECTe.*,cat_index.positionAScat_index_position,price_index.price,price_index.tax_class_id,price_index.final_price, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) ASminimal_price,price_index.min_price,price_index.max_price,price_index.tier_price,at_quantity_and_stock_status.valueASquantity_and_stock_statusFROMcatalog_product_entityASe INNER JOINcatalog_category_product_indexAScat_indexON cat_index.product_id=e.entity_id AND cat_index.store_id='1' AND cat_index.visibility IN(2, 4) AND cat_index.category_id='2' INNER JOINcatalog_product_index_priceASprice_indexON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0 INNER JOINcatalog_product_entity_intASat_quantity_and_stock_statusON (at_quantity_and_stock_status.entity_id=e.entity_id) AND (at_quantity_and_stock_status.attribute_id= '112') AND (at_quantity_and_stock_status.store_id= 0) WHERE (((IFNULL(at_quantity_and_stock_status.value, 0) = '1') ))

[tomispepe]

The comment above shows that only catalog_product_entity_int is queried to find wether a product is out of stock or in stock. However, that table is not updated by changing inventory in admin. I haven't tried running a product out of stock through the front end but I am concerned that the same issue probably exists under that circumstance as well. I will return here and update this issue at a later date after testing this scenario.

[perryholden]

It appears that when saving a product in the admin, the Magento\Catalog\Model\Product\Attribute\Backend\Stock::beforeSave() method takes the data property 'quantity_and_stock_status' which contains 'qty' and 'is_in_stock' data for the product and then sets that data to the product's 'stock_data' property, overwriting any existing values there. It then unsets 'quantity_and_stock_status' entirely. When it passes back to the parent::beforeSave() method, that method finds that 'quantity_and_stock_status' is now empty and sets its value to a default value which is '1'. This value gets set to the attribute database table (catalog_product_entity_int) and is always set to '1', regardless of whether or not the item is in stock. The _real_ values are written elsewhere.

When setting up a Product List widget, there is a conditions field that uses a Condition block (located in the Catalog Widget module). When setting up rules, the "quantity" rule (which allows you to specify 'in stock' or 'out of stock') is configured to use the 'quantity_and_stock_status' product attribute, but as we saw before, this is always being set to 1 regardless. The way the quantity and stock values are currently configured to be written to the database, this condition rule will never work properly. All products will be seen as "in stock".

As it stands, if you manually set a product's 'quantity_and_stock_status' attribute in the database table to '0', the widget will not display that product, as that is evidently how this was originally written to work. However, once you save that product again in the admin, that attribute will be overwritten and set to '1'.

So was this change intentional? If not, these beforeSave() methods will need to be rewritten to not incorrectly set the 'quantity_and_stock_status' attribute. But if so, then the Condition block will need to take this change in the location of "in stock" values into account when it sets up the rules that will ultimately go into the widget's instance, because currently the rule uses the 'quantity_and_stock_status' attribute.

[piotrekkaminski]

Internal issue MAGETWO-53445

[veloraven]

This issue was fixed in develop branch.
It also was fixed for ver. 2.1 - internal ticket - MAGETWO-57028. Fix will be available in one of the nearest releases.

[magento-engcom-team]

@tomispepe thank you for your report.
The fix for this issue is already fixed in 2.2.0 release

[kalusingh]

@magento-engcom-team
This issue is not fixed in Magento 2.2.1 version
"catalog_product_entity_int" table is not updating after change stock status

[lcsbaroni]

@magento-engcom-team any news?

[vteofilo]

This worked for me:

As I have a specific function to update inventory that extends \Magento\CatalogInventory\Model\StockRegistry, I called inside it, but if you use Magento itself you should create a plugin to be called after you have run the function Magento\CatalogInventory\Model\StockRegistry::updateStockItemBySku(). At each inventory update, this function updates the is_in_stock status of the parent product by adding the items of the child products:

`

public function syncProductStock(int $productId)
{
    $product = $this->productFactory->create();
    $product->load($productId);

    $productParent= $this->productFactory->create();
    $productParent->load(
        $this->objectManager->get(\Magento\ConfigurableProduct\Model\ResourceModel\Product\Type\Configurable::class)
            ->getParentIdsByChild($productId)
    );

    $childrenIds = array_values(
        $this->objectManager->get(\Magento\ConfigurableProduct\Model\ResourceModel\Product\Type\Configurable::class)
            ->getChildrenIds($productParent->getId())[0]
    );
    $sumQuantity = 0;
    foreach ($childrenIds as $childrenId) {
        $productChildren = $this->productFactory->create();
        $productChildren->load($childrenId);
        $sumQuantity += $this->objectManager->get(\Magento\CatalogInventory\Model\StockRegistry::class)
            ->getStockItem($childrenId)->getQty();
    }

    $isInStock = $sumQuantity > 0 ? 1 : 0;
    $product->addAttributeUpdate('quantity_and_stock_status', $this->getStockItem($productId)->getIsInStock(), $product->getStoreId());
    $productParent->addAttributeUpdate('quantity_and_stock_status', $isInStock, $product->getStoreId());

    $parentStockItem = $this->getStockItem($productParent->getId());
    $parentStockItem->setIsInStock($isInStock);
    $this->stockItemRepository->save($parentStockItem);
}`

Hope this helps! Thank you!