0

This is my collection:

$blacklist = Mage::getModel('mynamespace_mymodule/customers')->getCollection()
            ->addFieldToFilter('store_id', $order->getStoreId())
            ->addFieldToFilter('status', 1) 
            ->addFieldToFilter(
                ['email', 'last_name', 'first_name', 'ip_address', 'address'],
                [
                    ['email', 'eq' => $order->getCustomerEmail() ],
                    [
                        ['last_name',  $address->getData('lastname') ],
                        ['first_name',  $address->getData('firstname') ],
                    ],
                    ['ip_address', 'eq' => $currentIpAddress ],
                    ['address', 'like' => '%'. $orderAddressStreet],
                ]
            )
            ->addFieldToSelect('type');

This is the where condition:

(`store_id` = '1') AND (`status` = '1') AND 
((`email` = '[email protected]') OR
(((((`last_name` = 'last_name') OR (`last_name` = 'asdasdas'))) OR
(((`last_name` = 'first_name') OR (`last_name` = 'sdsadasd'))))) OR
(`first_name` = '127.0.0.1') OR (`ip_address` LIKE '%sdfafsdfd 21 asdasd') OR (`address` = ''))

The where condition is totally messed up. I would like to have something like:

(
    (email = 'emailaddress') OR 
    ('last_name' = 'lastname' AND 'firstname' = 'firstname') OR
    ('ip_addrres' = 'myip') OR
    ('address' LIKE '%address')
)

How can I change my collection to get this where condition?

1 Answer 1

0

For OR conditions you can use the following snippet

$collection->addAttributeToFilter(
    array(
        array('attribute'=> 'someattribute','like' => 'value'),
        array('attribute'=> 'otherattribute','like' => 'value'),
        array('attribute'=> 'anotherattribute','like' => 'value'),
    )
);

I have created the same conditions like your requirement but with the Product collection. So, you can modify the model and the conditions in the below snippet to get the SQL query you have asked.

<?php


//   File path - YOUR_MAGENTO_ROOT_DIRECTORY/test.php


require_once('app/Mage.php');
Mage::app();

$customers = Mage::getModel('catalog/product')->getCollection();
$customers->addAttributeToFilter(
    array(
        array('attribute'=> 'sku', 'eq' => 'sku1'),
        array('attribute'=> 'name', 'eq' => 'Product name'),
        array('attribute'=> 'description', 'like' => '%Some text%'),
    )
);
$customers->getSelect()->orWhere("color='blue' AND manufacturer='brand1'");

echo $customers->getSelect();

This will print the following SQL query:

SELECT `e`.`entity_id`, `e`.`type_id`, `e`.`attribute_set_id`, IF(at_description.value_id > 0, at_description.value, at_description_default.value) AS `description` FROM `catalog_product_flat_1` AS `e` INNER JOIN `catalog_product_entity_text` AS `at_description_default` ON (`at_description_default`.`entity_id` = `e`.`entity_id`) AND (`at_description_default`.`attribute_id` = '72') AND `at_description_default`.`store_id` = 0 LEFT JOIN `catalog_product_entity_text` AS `at_description` ON (`at_description`.`entity_id` = `e`.`entity_id`) AND (`at_description`.`attribute_id` = '72') AND (`at_description`.`store_id` = 1) WHERE (e.status = 1) AND ((`e`.`sku` = 'sku1') OR (`e`.`name` = 'Product name') OR (IF(at_description.value_id > 0, at_description.value, at_description_default.value) LIKE '%Some text%')) OR (color='blue' AND manufacturer='brand1')

As you can see, the where condition is now in the same way as you want.

Updated Answer

<?php


//   File path - YOUR_MAGENTO_ROOT_DIRECTORY/test.php


require_once('app/Mage.php');
Mage::app();

$sku = 'sku1';
$name = 'Product name';
$description = '%Some text%';
$color = 'blue';
$manufacturer = 'brand1';

$products = Mage::getModel('catalog/product')->getCollection()
            ->addAttributeToFilter('weight', '100');

$products->getSelect()->where('(sku = ?) 
    OR (name = ?) 
    OR (description like ?) 
    OR (color = ? AND manufacturer = ?)', $sku, $name, $description, $color, $manufacturer);

echo $products->getSelect();
3
  • yeah but the with would smth like conditions AND (Condition 1 OR Condition 2) OR (Condition 3 AND Condition 4) instead of conditions AND (Condition 1 OR Condition 2 OR (Condition 3 AND Condition 4) ) Commented Dec 7, 2017 at 12:32
  • I have updated my answer. This updated code can give you the required SQL Commented Dec 7, 2017 at 15:02
  • @Chester Was it useful for you. If so, please accept it as the answer. If not, post the issue you have faced Commented Dec 12, 2017 at 11:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.