1

I am applying filter on order collection. I am stuck in a situation, I have added a custom attribute to "customer address". that is in table "customer_address_entity_varchar", now I have to join following three tables, (1)sales_flat_order (2)sales_flat_order_address (3) customer_address_entity_varchar. In a such way as shown bellow, I know that is not right thing.

$orderCollection = Mage::getModel('sales/order')->getCollection();
            //Filter for excluding Stores
        if (!empty($excludeStoresIds)) {
            $excludeStoresIds = explode(',', $excludeStoresIds);
            $excludeStoresIds = array_map('trim', $excludeStoresIds); //Remove white spaces
            $orderCollection->getSelect()->joinLeft(array('sfoa' => 'sales_flat_order_address'), 'main_table.entity_id = sfoa.parent_id', "");
            $orderCollection->addFieldToFilter('sfoa.address_type', array('eq' => 'shipping'));
            $orderCollection->getSelect()->joinLeft(array('sfoa' => 'sales_flat_order_address','caev' => 'customer_address_entity_varchar'), 'sfoa.customer_address_id = caev.entity_id', "");
            $orderCollection->addFieldToFilter('caev.value', array('nin' => $excludeStoresIds));
        }

Here $excludeStoresIds is an array

2
  • I tried This, but not working Feb 9, 2016 at 9:52
  • 1
    Well in your code above, there is an issue, you gave the same alias to sales_flat_order_address. Name the second one sfoa2 for example, and try again. (you will need to change sfoa to sfoa2 in every reference in this join) Feb 9, 2016 at 10:24

1 Answer 1

1

Thanks @Mayers

$orderCollection->getSelect()->joinLeft(array('sfoa' => 'sales_flat_order_address'), 'main_table.entity_id = sfoa.parent_id', "");
            $orderCollection->addFieldToFilter('sfoa.address_type', array('eq' => 'shipping'));
            $orderCollection->getSelect()->joinLeft(array('caev' => 'customer_address_entity_varchar'), 'caev.entity_id = sfoa.customer_address_id', "");
            $orderCollection->addFieldToFilter('caev.value', array('nin' => $excludeStoresIds));

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.