cancel
Showing results for 
Search instead for 
Did you mean: 

How to add nested AND and OR conditions to a product collection?

   Did you know you can see the translated content as per your choice?

Translation is in progress. Please check again after few minutes.

How to add nested AND and OR conditions to a product collection?

I could filter a product collection by (attribute A: condition A OR condition B) AND (attribute B: condition C OR condition D) with the following codes:

$collection->addAttributeToFilter('attribute A', [
  [condition here...], //e.g. ['neq' => 'x']
  [condition here...] //e.g. ['gt' => 'y']
]);$collection->addAttributeToFilter('attribute B', [
  [condition here...],
  [condition here...]
]);

But I have no idea how to achieve this: (attribute A: condition A AND condition B) OR (attribute B: condition C and condition D)

I have also tried to use search criteria builder, but still no luck so far.

4 REPLIES 4

Re: How to add nested AND and OR conditions to a product collection?

Hi @motive_power 

Please try once using following example:

// AND QUERY
$collection->addAttributeToFilter('sku', array('like' => '%ch%'));


//OR QUERY
$collection->addAttributeToFilter(array(
	array(
		'attribute' => 'sku',
		'like' => '%ch%'),
	array(
		'attribute' => 'status',
		'eq' => '1')
));

You can log your query using following methods: 

echo $collection->printLogQuery(true);

OR 

echo $collection->getSelect();

I Hope it will help you!

Re: How to add nested AND and OR conditions to a product collection?

@motive_power 

For your case try the following:

$collection->addAttributeToFilter(array(
	array(
		'attribute' => 'attribute A',
		'like' => '%condition A%'),
	array(
		'attribute' => 'attribute A',
		'like' => '%condition B%'),
));
$collection->addAttributeToFilter(array(
	array(
		'attribute' => 'attribute B',
		'like' => '%condition C%'),
	array(
		'attribute' => 'attribute B',
		'like' => '%condition D%'),
));

echo $collection->getSelect();

I Hope it will help you!

Re: How to add nested AND and OR conditions to a product collection?

@Vimal Kumar Thanks for your reply.

But your solution is giving me something like this:

 

WHERE (( `Attribute A` `Condition A` OR `Attribute A` `Condition B`) AND (( `Attribute B` `Condition C` OR `Attribute B` `Condition D`))

 

But what I am looking for should be:

 

WHERE (( `Attribute A` `Condition A` AND `Attribute A` `Condition B`) OR (( `Attribute B` `Condition C` AND `Attribute B` `Condition D`))

 

Re: How to add nested AND and OR conditions to a product collection?

The above quest not looks feasible using this method.

We can use following method for complex query:

Zend_Db_Expr :

For much more complicated constructs you can build your own where clause using Zend_Db_Expr. For instance :

$collection->addAttributeToSelect(‘attributeA’)
->addAttributeToSelect(‘attribute2’);

$collection->getSelect()->where(new Zend_Db_Expr("(e.created_at > '2013-01-01 00:00:00' OR e.created_at <'2012-01-01 00:00:00)"));

Change your conditions accordingly in where condition.

Please let me know if it will work.