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
addFieldToFilterfor 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 support@webkul.com.
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.