Complex query implying dates

Question

Hi, I'd like to return the customers from Magento who where created the day before OR who where updated the day before. I tried to play with addFieldToFilter, without any success.

I also tried to manipulate Zend_Db_Select, no success.

So now I'm stuck!

Here are some of my tries :

$customer = Mage::getModel('customer/customer');
$customers = $customer
    ->getCollection()
    ->getSelect()
    ->where("updated_at >= ? AND updated_at <= ?",$this->getFrom(), $this->getTo())
    ->orWhere("e.created_at >= ? AND e.created_at <= ?", $this->getFrom(), $this->getTo());

Or

->addFieldToFilter(
    array(
        array('attribute'=>'updated_at', 'gteq'=>$this->getFrom()),
        array('attribute'=>'created_at', 'gteq'=>$this->getFrom())
    ),
    '',
    'left'
);

Thanks

Answer 1

It is enough to use updated_at as your filter attribute, because it is set to current datetime when a user is created. So by filtering with this field you will get both new users and those who are not new but were updated in the given period. Here's the code to look for users updated or created during the last 24 hours:

$customers = Mage::getModel('customer/customer')->getCollection();
$customers->addAttributeToFilter('updated_at', array('gt' => date("Y-m-d H:i:s", time()-60*60*24)));

foreach($customers as $customer) {
    //do sth
}

Answer 2

I'd recommend against directly manipulating the select unless it's absolutely necessary and you know exactly what's going on behind the scenes in your version of Magento.

The following syntax should handle the tricky parts for you

$c = Mage::getModel('customer/customer')
->getCollection()
->addAttributeToFilter(array(               
    array('attribute'=>'updated_at','from'=>'2010-05-12','to'=>'2010-05-30'),
    array('attribute'=>'created_at','from'=>'2010-05-12','to'=>'2010-05-13')
));

var_dump( (string) $c->getSelect());
var_dump(count($c));

all you'll need to do is drop in the date ranges you want.

Answer 3

Thanks to Alan and Silvo, here is what I wrote :

->addAttributeToFilter(array(               
    array('attribute'=>'updated_at','from'=>$this->getFrom(),'to'=>$this->getTo())
));

Both answers were usefull. Thank you!