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

1 Answer 1


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, 2018 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, 2018 at 22:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.