Filter Sales/order collection

Question

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)

Answer 1

You can directly query

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

this should do the trick.