Add multiple OR conditions in Magento 2 collection

Introduction

Magento 2 collections are widely used to fetch data from the database in a structured and optimized way.

By default, when we add multiple filters to a collection, Magento applies them using AND conditions. However, there are many real-world scenarios where we need to apply OR conditions instead.

For example, you may want to:

• Fetch products with status enabled OR visibility catalog
• Load orders with state complete OR closed
• Filter customers by multiple attributes

In this blog, we’ll learn how to add multiple OR conditions in Magento 2 collections using different approaches.

---

Default Behavior: AND Condition

When you add filters like this, Magento applies an AND condition:

$collection->addFieldToFilter('status', 1)
           ->addFieldToFilter('visibility', 4);

This generates SQL similar to:

WHERE status = 1 AND visibility = 4

---

Using an OR Condition with addFieldToFilter

To apply an OR condition, you can pass an array of conditions inside addFieldToFilter.

Example: Single Field OR Condition

$collection->addFieldToFilter(
    'status',
    ['eq' => 1, 'eq' => 2]
);

However, this works only for limited cases.

---

Add Multiple OR Conditions (Recommended Way)

To apply multiple OR conditions across different fields, use the array format:

$collection->addFieldToFilter(
    ['status', 'visibility'],
    [
        ['eq' => 1],
        ['eq' => 4]
    ]
);

This produces SQL like:

WHERE (status = 1 OR visibility = 4)

---

Using OR Conditions with LIKE

You can also use OR conditions with LIKE queries:

$collection->addFieldToFilter(
    ['sku', 'name'],
    [
        ['like' => '%shirt%'],
        ['like' => '%shirt%']
    ]
);

This is useful for search-type functionality.

---

Combining AND and OR Conditions

Magento allows you to mix AND and OR conditions as well:

$collection->addFieldToFilter('status', 1)
           ->addFieldToFilter(
               ['sku', 'name'],
               [
                   ['like' => '%shirt%'],
                   ['like' => '%shirt%']
               ]
           );

This results in:

WHERE status = 1 AND (sku LIKE '%shirt%' OR name LIKE '%shirt%')

---

Using Zend_Db_Expr (Advanced)

For complex conditions, you can use SQL expressions:

$collection->getSelect()->where(
    '(status = ? OR visibility = ?)',
    [1, 4]
);

Important: Use this approach carefully, as it bypasses Magento’s abstraction layer.

---

Best Practices

• Prefer addFieldToFilter for better compatibility
• Use SQL expressions only for complex scenarios
• Always test the generated SQL using $collection->getSelect()->__toString()
• Avoid raw SQL unless necessary

---

Conclusion

Adding multiple OR conditions in Magento 2 collections is simple once you understand the array-based syntax. This approach keeps your code clean, readable, and aligned with Magento best practices.

Mastering collection filtering helps you build efficient modules, custom reports, and optimized business logic in Magento 2.

If you require technical support, feel free to email us at [email protected].

Additionally, explore a wide array of solutions to boost your store’s capabilities by visiting the Adobe Commerce modules section.

For expert advice or to create tailored features, hire Adobe Commerce Developers for your project.