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

[motive_power]

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.

[Vimal Kumar]

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!

[Vimal Kumar]

@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!

[motive_power]

@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`))

[Vimal Kumar]

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.