I need an ORM query to get all past successful orders with a specific billing street address, city, state and zip code? ie:

$street = '999 west rd';
$city = 'my town';
$state = 'New York';
$zip = '90001';

//write ORM query to get all past
//successful orders with all those exact values

Note that the address components are not indexed in the DB, so the following query will scan all the success orders in the address table.

$collection = Mage::getResourceSingleton('sales/order_collection')
    ->addFieldToFilter('main_table.state', array('in'=>array('complete', 'processing'))) // successful orders, is 'closed' state success?
    ->addFieldToFilter('a.street', $street)
    ->addFieldToFilter('a.city', $city)
    ->addFieldToFilter('a.region', $state)
    ->addFieldToFilter('a.postcode', $zip)
    ->addFieldToFilter('a.address_type', 'billing'); // another type is 'shipping'
    array('a' => $collection->getTable('sales/order_address')),
foreach ($collection as $item) {
    $result[$item->getId] = $item->getData();
    // etc.
  • thx. why both complete and processing? – Ryan Aug 31 '18 at 2:36
  • Well, it depends on what you considered as successful orders. You can filter for whatever states or statuses as required. – kiatng Sep 2 '18 at 22:39

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.