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` = 'asdsasaias8@yahoo.ro') 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?

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) ) Dec 7 '17 at 12:32
  • I have updated my answer. This updated code can give you the required SQL Dec 7 '17 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 Dec 12 '17 at 11:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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