get customer who have not placed order from last 3 months but in optimized way

Question

I need to optimized my answer.

$start=2019-05-18 00:00:01;
$end=2019-05-18 23:59:59;

$customerArr=array();
$customers = Mage::getModel('customer/customer')->getCollection()
    ->addFieldToFilter('created_at', array("gteq" => $greaterOrEqualDate))
    ->addAttributeToSelect('is_xyz_email_sent')
    ->addAttributeToFilter('is_xyz_email_sent', array('neq' => 1));;



foreach ($customers as $customer){
    $lastOrder = Mage::getModel('sales/order')->getCollection()
        ->addFieldToFilter('customer_email',array("eq"=>$customer->getEmail()))
        ->addFieldToFilter('customer_email',array("nlike"=>'%example.com'))

        ->setOrder('entity_id','DESC')
        ->setPageSize(1)
        ->getFirstItem();



    if($lastOrder->getCustomerEmail() && $lastOrder->getCreatedAt() >= $start && $lastOrder->getCreatedAt() <= $end) {


        $unsubModule=Mage::getModel('unsubscribe/unsubscribe')->getCollection()
            ->addFieldToFilter('_id',$campaign_id)
            ->addFieldToFilter('_email',$customer->getEmail());


        if($unsubModule->getSize()==0) {
            array_push($customerArr, $lastOrder->getCustomerEmail());
        }

    }
}

This gives me right output but I need it optimized way because I m working with large database

Answer 1

You can try with flat sql query something like below,

select e.*,sfoi.* from sales_flat_order e
inner join sales_flat_order_item sfoi on (e.entity_id=sfoi.order_id)
where e.customer_email = '$customer->getEmail()';