addFieldToFilter() with OR Condition in magento2

Question

$collectionData = $this->_productCollectionFactory->create()->addAttributeToSelect('*')
    ->addFieldToFilter(['attribute1', 'attribute2'],
        [
            ['in' => $attribute1],
            ['nin' => $attribute2],
        ]);

I want to get collection based on "OR condition" on three attribute for example a product which has color yellow or size small but not manufacture china.

Answer 1

I assume that in your example, yellow, small and china are options in dropdowns.
You will need their ids first, but let's assume the are $yellowId, $smallId and $chinaId.

You can try this filtering.

->addFieldToFilter(['color', 'size', 'country'],
    [
        ['eq' => $yellowId],
        ['eq' => $smallId],
        ['neq' => $chinaId]
    ]);

Answer 2

Try this, It work for me

$collection->addAttributeToFilter([
     ['attribute' => 'attribute1','null' => true ],
     ['attribute' => 'attribute1','gteq' => 1 ]
]);
    // Don't change 'attribute', It will be used at AbstractCollection: 373
    // WHERE attribute1 = NULL OR attribute1 >= 1

Answer 3

This should give you all items that are not made in china and that have either color yellow or size small:

$collection
    ->addFieldToFilter('manufacturer', ['neq' => $chinaId])
    ->addFieldToFilter(
        ['color', 'size'],
        [
            ['eq' => $yellowId],
            ['eq' => $smallId]
        ]
);

Answer 4

I know the original question has been answered before, but if you want to add OR condition to a simple field to a flat collection, this is the correct way to do it:

$this->addFieldToFilter(
    'published_date', 
    [
        [
            'lteq' => date('Y-m-d H:m:i')
        ], 
        [
            'null' => true
        ], 
        [
            'from' => '9999-01-01 00:00:00',
            'to' => '9999-12-31 23:59:59',
        ]
    ]
);

just for reference: this will appear in the query as ((published_date <= '2020-03-24 18:03:57') OR (published_date IS NULL))

of course, you can add as many conditions to the array if follow the correct pattern. Also be sure that using from and to conditions, those are need to be in the same array as above

Answer 5

From the description of the method: \Magento\Eav\Model\Entity\Collection\AbstractCollection::addAttributeToFilter which is the same with addFieldToFilter method from the same class. Product entity is implemented with EAV.

// If $attribute is an array will add OR condition with following format:
array(
   array('attribute'=>'firstname', 'like'=>'test%'),
   array('attribute'=>'lastname', 'like'=>'test%'),
)
public function addAttributeToFilter($attribute, $condition = null, $joinType = 'inner')
{
...
}

Answer 6

 $collection->addFieldToFilter(['status','status'],[['nin'=>$allnonactiveorderstatus],['null'=>true]])