8
$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.

3
  • is this collection is EAV collection or flat collection
    – Amit Bera
    Dec 30 '16 at 14:12
  • getting data from product collection
    – chin
    Dec 30 '16 at 14:17
  • 1
    use addAttributeToFilter() instead of addFieldToFilter
    – Amit Bera
    Dec 30 '16 at 14:21
27

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]
    ]);
2
  • 1
    You can also see \Magento\Catalog\Model\ResourceModel\Product\Collection::addFilterByRequiredOptions for an example with a single attribute. Dec 2 '20 at 21:17
  • 1
    Marius, you are a saint of the Magento universe Jan 11 at 17:18
5

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]
        ]
);
4

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')
{
...
}
2
  • This is the only method that worked form me
    – Char
    Dec 21 '17 at 14:34
  • Aaron Allen answer did not work for me, the above did. Nov 14 '18 at 21:45
3

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
4
  • 1
    Worked perfectly. Thanks.
    – Dacod
    Feb 3 '20 at 19:04
  • I don't know why, but this filter doesn't work for me: [ ['attribute' => 'sku','nlike' => '%test123%' ], ['attribute' => 'sku','nlike' => '%test234%' ], ] Do you have any idea why?
    – hallleron
    May 28 at 7:44
  • @hallleron maybe you need to re-check your data, I have a quick check on Magento 2.3.5-p2. It's still work perfectly May 30 at 15:02
  • @hallleron print_r($collection->getSelectSql(true)); you can use this to check your query first. Make sure your collection is newly created by the factory and carefully by the filters added before May 30 at 15:11
3

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

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.