Magento 2 : AND condition in addAttributeToFilter not giving desired result

Question

According to answer to this related question the AND condition in addAttributeToFilter is added by adding consecutive addAttributeToFilter so my statement is something like;

$_productCollection->addAttributeToSelect('*')->addAttributeToFilter('manufacturer', array(
                    array('nin' => $brands)
                ))->addAttributeToFilter('warehouse1', array(
                    array('nin' => 0)
                ))->addAttributeToFilter('warehouse4', array(
                    array('nin' => 0)
                )); // I want product to be excluded when both warehouse1 and warehouse4 is 0

As in the code above I need the product to be removed from collection when both "warehouse1" and "warehouse4" are simultaneously 0 but instead the code is first filtering collection based on "warehouse1" and then the filtered collection is filtered again for "warehouse4".
As a result I am also excluding products which have 0 for "warehouse1" but not a 0 for "warehouse4".
Please help me resolve this.

EDIT:

The answer by @Murtuza Zabuawala worked for me, it was not working before as some of the attributes had null for them even though they were supposed to be 0 by default. However, this does not answer the core question of how to add AND to addAttributeToFilter but nonetheless I am going to award the bounty.

Answer 1

try below code for and condition in two or more attribute

    $_productCollection->addAttributeToSelect('*')
    ->addAttributeToFilter('manufacturer', array(
                array('nin' => $brands)
            ))->addAttributeToFilter(
              array(
                array('attribute' => 'warehouse1', 'neq' => 0),
                array('attribute' => 'warehouse4', 'neq' => 0),
            ));

Edit

According TO your answer I have Improved your answer

$cloned_collection = clone $_productCollection;
$product_ids = $cloned_collection->addAttributeToSelect('*')->addAttributeToFilter('warehouse1', array(
                array('eq'=> '0'),
                array('null' => 'this_value_doesnt_matter'),
                array('eq' =>'0.00')    
            ))->addAttributeToFilter('warehouse4', array(
                array('eq'=> '0'),
                array('null' => 'this_value_doesnt_matter'),
                array('eq' =>'0.00')    
            ))->getAllIds();

$_productCollection =  $_productCollection->addAttributeToSelect('*')->addAttributeToFilter('entity_id',array('in' => $product_ids));

Edit2

$_productCollection->addAttributeToSelect('*')
        ->addAttributeToFilter('manufacturer', array(
                array('nin' => $brands)
            ))
        ->addAttributeToFilter('warehouse1',array('neq' => ''))
        ->addAttributeToFilter('warehouse4',array('neq' => ''))  //check wareHouse != null and add innerJoin
        ->getSelect()->where("at_warehouse1 <> 0 AND at_warehouse4 <> 0 ");    

Answer 2

I have found a workaround though it is not elegant in any way and I still think there should be a way to do it in one step, Instead of filtering and removing the 0 in the first step itself I have made a collection which has both attributes 0 and it works as it is applying filter over the filter before [as I have explained in question].Then I am getting all skus from this collection to filter the required collection like below ;

$cloned_collection = clone $_productCollection;
$cloned_collection = $cloned_collection->addAttributeToSelect('*')->addAttributeToFilter('warehouse1', array(
                    array('eq'=> '0'),
                    array('null' => 'this_value_doesnt_matter'),
                    array('eq' =>'0.00')    
                ))->addAttributeToFilter('warehouse4', array(
                    array('eq'=> '0'),
                    array('null' => 'this_value_doesnt_matter'),
                    array('eq' =>'0.00')    
                ))->getData();
                for($i=0;$i<count($cloned_collection);$i++){
                    $skuArray[$i] = $cloned_collection[$i]['sku']; 
                }
$_productCollection =  $_productCollection->addAttributeToSelect('*')->addAttributeToFilter('sku', array('nin' => $skuArray));

Please let me know if someone has a better working solution.

Answer 3

In SQL you'd need warehouse1 != 0 OR warehouse4 != 0.

Or conditions are combined like this:

addFieldToFilter(['field1', 'field2'], [$condition1, $condition2]);

So in your case it is:

addAttributeToFilter(
    ['warehouse1', 'warehouse4'],
    [['neq' => 0], ['neq' => 0]],
);

Update: This syntax to combine OR conditions does not work anymore in Magento 2. Th correct way has been described by @MurtuzaZabuawala in his original answer:

addAttributeToFilter(
    [
        [
            'attribute' => 'warehouse1',
            'neq' => 0
        ],
        [
            'attribute' => 'warehouse4',
            'neq' => 0
        ],
    ]
);

This results in (warehouse1 != 0 OR warehouse4 != 0), which is the condition you need according to your description.