Get disable products in product collection in magento2.2

[developer-vtnetzwelt]

I am trying to get product collection with all enabled/disabled products. It works fine until I upgrade Magento to 2.2.2. Now It's not fetching disabled products in collection.

Preconditions
Magento v2.2.2
Cache enabled

Steps to reproduce
Here is my code that I have applied -
`class Index extends \Magento\Framework\App\Action\Action
{
protected $_productCollectionFactory;

public function __construct(
    \Magento\Catalog\Model\ResourceModel\Product\CollectionFactory $productCollectionFactory,
    \Magento\Framework\ObjectManagerInterface $objectmanager,

) {
    $this->_objectManager = $objectmanager;
    parent::__construct($context);
    $this->_productCollectionFactory = $productCollectionFactory;
}

public function execute()
{
    $collection = $this->_productCollectionFactory->create()
                ->addAttributeToSelect('*')
                ->addAttributeToFilter('diamond_vendor', array('in'=>$vendor))
                ->addAttributeToFilter('status', array('in'=>array(1,2)))
                ->addAttributeToFilter('entity_id', array('nin'=>$notin_ids))
                ->load();
}

}`

here filter ->addAttributeToFilter('status', array('in'=>array(1,2))) , I applied after up-gradation.

In this I also tried with -
$collection->getSelect()->where('stock_status_index.stock_status IN(1,0)');
But its not making any effect.

Expected result

1. It should all products that have status either 'Enable' or 'Disable'

Actual result

1. Its returning only 'Enable' products.

In Admin - Stores > Configuration > Catalog > Inventory > Stock Options > Display Out of Stock Products-> If I set this to yes then it fetching all 'Enable/Disable' products but I don't want to set this setting to 'Yes'.

Plz help me in this to sort it out, stucked in this problem more than 2 days. Need help. Thank you.

[developer-vtnetzwelt]

Got a solution for the above issue via below trick -

public function execute()
{
    $collection = $this->_productCollectionFactory->create()
                ->addAttributeToSelect('*')
                ->addAttributeToFilter('diamond_vendor', array('in'=>$vendor))
                ->addAttributeToFilter('entity_id', array('nin'=>$notin_ids))
                ->load();
    //**applied below process to load disable products with default 'enable' once.
    $collection->clear();
    $where = $collection->getSelect()->getPart('where');
    foreach ($where as $key => $condition)
     {
         if(strpos($condition, 'stock_status_index.stock_status = 1') !== false){
                 $updatedWhere[] = 'AND (stock_status_index.stock_status IN (1,0))';
          } else {
                  $updatedWhere[] = $condition;
           }	
      }
     $collection->getSelect()->setPart('where', $updatedWhere);
     $collection->load();
}

Above process works for me. Hope it helps ... :)

[magento-engcom-team]

@developer-vtnetzwelt, thank you for your report.
We've acknowledged the issue and added to our backlog.

[magento-engcom-team]

Hi @shikhamis11. Thank you for working on this issue.
Looks like this issue is already verified and confirmed. But if you want to validate it one more time, please, go though the following instruction:

• 1. Add/Edit Component: XXXXX label(s) to the ticket, indicating the components it may be related to.

• 2. Verify that the issue is reproducible on 2.3-develop branch

  Details

  - Add the comment @magento-engcom-team give me 2.3-develop instance to deploy test instance on Magento infrastructure.
  - If the issue is reproducible on 2.3-develop branch, please, add the label Reproduced on 2.3.x.
  - If the issue is not reproducible, add your comment that issue is not reproducible and close the issue and stop verification process here!

• 3. Verify that the issue is reproducible on 2.2-develop branch.

  Details

  - Add the comment @magento-engcom-team give me 2.2-develop instance to deploy test instance on Magento infrastructure.
  - If the issue is reproducible on 2.2-develop branch, please add the label Reproduced on 2.2.x

• 4. If the issue is not relevant or is not reproducible any more, feel free to close it.

[developer-vtnetzwelt]

Hi @shikhamis11 @magento-engcom-team
I am not 100% sure but I believe this issue is coming due to the area under which this code is working.
Please check this class Index extends \Magento\Framework\App\Action\Action this code is working under frontend area so if we have flat catalog enabled, frontend is not suppose to have disabled products so it looks like functionality. It will be great if someone can confirm about it.
@shikhamis11 To verify this issue we need to check it on some other areas as well.

[shikhamis11]

hi @developer-vtnetzwelt
what I think this issue is coming due to restriction of out of stock product display at frontend . for this I think there is some conditions applied basically joins in collection at frontend which eventually removes the disabled product from collection along with out of stock products. I will more dig out this and show the queries

[magento-engcom-team]

@sdzhepa Thank you for verifying the issue.

Unfortunately, not enough information was provided to acknowledge ticket. Please consider adding the following:

• Add "Component: " label(s) to this ticket based on verification result. If uncertain, you may follow the best guess
• Update issue content to follow format required by Issue Reporting Guidelines

Once all required information is added, please add label "Issue: Confirmed" again.
Thanks!

[magento-engcom-team]

@shikhamis11 Thank you for verifying the issue.

Unfortunately, not enough information was provided to acknowledge ticket. Please consider adding the following:

• Update issue content to follow format required by Issue Reporting Guidelines

Once all required information is added, please add label "Issue: Confirmed" again.
Thanks!

[magento-engcom-team]

✅ Confirmed by @shikhamis11
Thank you for verifying the issue. Based on the provided information internal tickets MAGETWO-99053, MAGETWO-99054 were created

Issue Available: @shikhamis11, You will be automatically unassigned. Contributors/Maintainers can claim this issue to continue. To reclaim and continue work, reassign the ticket to yourself.

[abhi-ranosys]

I am working on this at #dmcdindia19

[m2-assistant]

Hi @abhi-ranosys. Thank you for working on this issue.
Looks like this issue is already verified and confirmed. But if you want to validate it one more time, please, go though the following instruction:

• 1. Add/Edit Component: XXXXX label(s) to the ticket, indicating the components it may be related to.

• 2. Verify that the issue is reproducible on 2.3-develop branch

  Details

  - Add the comment @magento-engcom-team give me 2.3-develop instance to deploy test instance on Magento infrastructure.
  - If the issue is reproducible on 2.3-develop branch, please, add the label Reproduced on 2.3.x.
  - If the issue is not reproducible, add your comment that issue is not reproducible and close the issue and stop verification process here!

• 3. Verify that the issue is reproducible on 2.2-develop branch.

  Details

  - Add the comment @magento-engcom-team give me 2.2-develop instance to deploy test instance on Magento infrastructure.
  - If the issue is reproducible on 2.2-develop branch, please add the label Reproduced on 2.2.x

• 4. If the issue is not relevant or is not reproducible any more, feel free to close it.

[blob84]

The same issue is present in magento 2.3.1 .

[ghost]

Hi @magento-engcom-team I have get same issure in magento 2.3.1.

/** @var \Magento\Catalog\Model\ResourceModel\Product\Collection $collection */
        $collection = $this->productFactory->create()->getCollection();

        $collection->addAttributeToSelect("*")
                            ->addAttributeToFilter("status", array("eq" => $status))
                            ->addAttributeToFilter("visibility", array("in" =>[2,4] ))
                            ->addAttributeToFilter("sku", array("in" => $productSkus));
        $collection->getSelect()->order(new \Zend_Db_Expr('FIELD(e.sku, ' . $txtSkus.')'));
        return $collection;

print collection sql to log :
SELECT 1 AS status, e.entity_id, e.attribute_set_id, e.type_id, e.created_at, e.updated_at, e.sku, e.entity_id, e.attribute_set_id, e.type_id, e.row_id, e.allow_open_amount, e.catalog_code, e.cost, e.created_at, e.description, e.email_template, e.giftcard_amounts, e.giftcard_type, e.gift_message_available, e.gift_wrapping_available, e.gift_wrapping_price, e.has_options, e.image, e.image_label, e.is_redeemable, e.lifetime, e.links_exist, e.links_purchased_separately, e.links_title, e.msrp, e.msrp_display_actual_price_type, e.name, e.news_from_date, e.news_to_date, e.open_amount_max, e.open_amount_min, e.price, e.price_type, e.price_view, e.required_options, e.sap_code, e.short_description, e.sku, e.sku_type, e.small_image, e.small_image_label, e.special_from_date, e.special_price, e.special_to_date, e.swatch_image, e.tax_class_id, e.thumbnail, e.thumbnail_label, e.ts_dimensions_height, e.ts_dimensions_length, e.ts_dimensions_width, e.updated_at, e.url_key, e.url_path, e.use_config_email_template, e.use_config_is_redeemable, e.use_config_lifetime, e.visibility, e.weight, e.weight_type FROM catalog_product_flat_1 AS e WHERE (e.visibility IN(2, 4)) AND (e.sku IN('344412-E')) ORDER BY FIELD(e.sku, "344412-E")
we can know the product collection get data from flat table in the sql,but I didn't find the status field from the flat table,I think this is a bug.

[cmple]

@abhi-ranosys, @developer-vtnetzwelt, @magento-engcom-team I think the issue is inside of the following class:
Magento\Catalog\Ui\DataProvider\Product\ProductCollection

To reproduce -> In admin filter products by Status = Disabled

The following function is missing an attribute_id condition:

Original:

    /**
     * Add attribute to filter by all stores
     *
     * @param Attribute $attributeModel
     * @param array $condition
     * @return void
     */
    private function addAttributeToFilterAllStores(Attribute $attributeModel, array $condition)
    {
        $tableName = $this->getTable($attributeModel->getBackendTable());
        $entity = $this->getEntity();
        $fKey = 'e.' . $this->getEntityPkName($entity);
        $pKey = $tableName . '.' . $this->getEntityPkName($entity);
        $condition = "({$pKey} = {$fKey}) AND ("
            . $this->_getConditionSql("{$tableName}.value", $condition)
            . ')';
        $selectExistsInAllStores = $this->getConnection()->select()->from($tableName);
        $this->getSelect()->exists($selectExistsInAllStores, $condition);
    }

SELECT `e`.*, `at_qty`.`qty` FROM `catalog_product_entity` AS `e`
 LEFT JOIN `cataloginventory_stock_item` AS `at_qty` ON (at_qty.`product_id`=e.entity_id) AND (at_qty.stock_id=1)
 LEFT JOIN `catalog_product_entity_int` AS `status_t1` ON e.row_id=status_t1.row_id AND status_t1.attribute_id='94' AND status_t1.store_id='0'
 LEFT JOIN `catalog_product_entity_int` AS `status_t2` ON e.row_id=status_t2.row_id AND status_t2.attribute_id='94' AND status_t2.store_id='0'

WHERE ((EXISTS (SELECT 1 FROM `catalog_product_entity_int` WHERE 
((catalog_product_entity_int.row_id = e.row_id) AND (catalog_product_entity_int.value = '2'))))) 

AND (e.created_in <= '1559361600') AND (e.updated_in > '1559361600') ORDER BY IF(status_t2.value_id > 0, status_t2.value, status_t1.value) ASC
 LIMIT 20

Working version:

    /**
     * Add attribute to filter by all stores
     *
     * @param Attribute $attributeModel
     * @param array $condition
     * @return void
     */
    private function addAttributeToFilterAllStores(Attribute $attributeModel, array $condition)
    {
        $tableName = $this->getTable($attributeModel->getBackendTable());
        $entity = $this->getEntity();
        $fKey = 'e.' . $this->getEntityPkName($entity);
        $pKey = $tableName . '.' . $this->getEntityPkName($entity);
        $condition = "({$pKey} = {$fKey}) AND ("
            . $this->_getConditionSql("{$tableName}.value", $condition)
            . ' AND '
            . "{$tableName}.attribute_id = '{$attributeModel->getId()}'"
            . ')';
        $selectExistsInAllStores = $this->getConnection()->select()->from($tableName);
        $this->getSelect()->exists($selectExistsInAllStores, $condition);
    }

SELECT `e`.*, `at_qty`.`qty` FROM `catalog_product_entity` AS `e`
 LEFT JOIN `cataloginventory_stock_item` AS `at_qty` ON (at_qty.`product_id`=e.entity_id) AND (at_qty.stock_id=1)
 LEFT JOIN `catalog_product_entity_int` AS `status_t1` ON e.row_id=status_t1.row_id AND status_t1.attribute_id='94' AND status_t1.store_id='0'
 LEFT JOIN `catalog_product_entity_int` AS `status_t2` ON e.row_id=status_t2.row_id AND status_t2.attribute_id='94' AND status_t2.store_id='0' 

WHERE ((EXISTS (SELECT 1 FROM `catalog_product_entity_int` WHERE 
((catalog_product_entity_int.row_id = e.row_id) AND (catalog_product_entity_int.value = '2' AND catalog_product_entity_int.attribute_id = '94'))))) 

AND (e.created_in <= '1559361600') AND (e.updated_in > '1559361600') ORDER BY IF(status_t2.value_id > 0, status_t2.value, status_t1.value) ASC
 LIMIT 20

[magento-engcom-team]

Hi @developer-vtnetzwelt, @abhi-ranosys.

Thank you for your report and collaboration!

The related internal Jira ticket MC-20025 was closed as non-reproducible in 2.3-develop.
It means that Magento team either unable to reproduce this issue using provided Steps to Reproduce from the Description section on clean Magento instance or the issue has been already fixed in the scope of other tasks.

But if you still run into this problem please update or provide additional information/steps/preconditions in the Description section and reopen this issue.