0

I am trying to get data where if my order state is complete and delivery_at is between date1 and date 2.

At the same time I also want to filter when state = canceled and delivery_at is between date3 and date4.

Point is that there are two conditions to filer : when (state = A and delivery_at = X) OR (state = 'canceled' and delivery_at = Y)

The code which I wrote is :

orderCollection = Mage::getModel('sales/order')->getCollection()->getSelect()->where(
                   new \Zend_Db_Expr("(state = 'complete') OR (state = 'canceled')")

In the above code, I haven't done filtering on delivery_at, as the above query is failing even then, as the count I get is 0;

So, how can I do in this current situation.

EDIT :: MY EXACT QUERY

$orderCollection = Mage::getModel('sales/order')->getCollection();
    $orderCollection->getSelect()->where(
                    new Zend_Db_Expr("(state = 'complete' AND DATE(delivery_at) >= '{$from}' AND DATE(delivery_at) <= '{$to}') OR (state = 'canceled' AND DATE(delivery_at) >= '{$from}' AND DATE(delivery_at) <= '{$to}'))")
                );

Error message :

Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 2, query was: SELECT `main_table`.* FROM `sales_flat_order` AS `main_table` WHERE ((state = 'complete' AND DATE(delivery_at) >= '2020-02-25' AND DATE(delivery_at) = '2020-02-25' AND DATE(delivery_at) ";i:1;s:2171:"#0 /var/www/html/fresh/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
1

You can directly query

Mage::getModel('sales/order')->getCollection()->getSelect()->where("(state = 'complete') OR (state = 'canceled')")

this should do the trick.

|improve this answer|||||
  • Thanks for the answer. What is happening is if I put the OR, and after that anything in bracket it throws an error. I have updated the answer with my exact query and the error log. – arqam Mar 26 at 5:49
  • Sorry, my bad. Missed a bracket. – arqam Mar 26 at 5:52

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.