Magento – Joining address fields to order

collection;ee-1.13sales

I am trying to export orders and address using the following code:

    $collection = Mage::getModel('sales/order')->getCollection();
    $collection->addFieldToFilter('store_id', 3);
    if (!$this->isAll()) {
        $collection->addFieldToFilter('updated_at', array('from' => date('Y-m-d', strtotime('-1 day'))));
        $collection->addFieldToFilter('updated_at', array('to' => date('Y-m-d')));
    }

    $addressCollection = Mage::getModel('customer/address')->getCollection()->addAttributeToSelect('*');
    $addressCollection->getSelect()->limit(1);
    $address = $addressCollection->getFirstItem();
    foreach ($address->getAttributes() as $attribute) {
        if (is_null($attribute->getAttributeID()) || is_null($attribute->getFrontendLabel()) || ($attribute->getFrontendLabel() == '')) {
            continue;
        }
        $collection->joinAttribute($attribute->getName() . '_billing', 'order_address/' . $attribute->getName(), 'billing_address_id', null, 'left');
        $collection->joinAttribute($attribute->getName() . '_shipping' , 'order_address/' . $attribute->getName(), 'shipping_address_id', null, 'left');
    }

    echo $collection->getSelect();

But my select does not contain the joined fields – the output is:

SELECT `main_table`.* FROM `sales_flat_order` AS `main_table` WHERE (store_id = '3') AND (updated_at >= '2014-04-15') AND (updated_at <= '2014-04-16')

Best Answer

The Mage_Sales_Model_Resource_Collection_Abstract::joinAttribute method of sales models is empty and thus without any effect:

/**
 * Backward compatibility with EAV collection
 *
 * @todo implement join functionality if necessary
 *
 * @param string $alias
 * @param string $attribute
 * @param string $bind
 * @param string $filter
 * @param string $joinType
 * @param int $storeId
 * @return Mage_Sales_Model_Resource_Collection_Abstract
 */
public function joinAttribute($alias, $attribute, $bind, $filter = null, $joinType = 'inner', $storeId = null)
{
    return $this;
}

Replace the second part with joins

    $shippingCols = array();
    $billingCols = array();
    foreach ($address->getAttributes() as $attribute) {
        if (is_null($attribute->getAttributeID()) || is_null($attribute->getFrontendLabel()) || ($attribute->getFrontendLabel() == '')) {
            continue;
        }
        $billingCols[] = 'bill_address.' . $attribute->getName() . ' as billing_' . $attribute->getName();
        $shippingCols[] = 'ship_address.' . $attribute->getName() . ' as shipping_' . $attribute->getName();
    }
    $collection->join(array('bill_address' => 'order_address'), 'main_table.billing_address_id = bill_address.entity_id', $billingCols);
    $collection->join(array('ship_address' => 'order_address'), 'main_table.shipping_address_id = ship_address.entity_id', $shippingCols);

Related Solutions

Magento – Joining to a Resource Model that doesn’t have a Select

The reporting collections work a bit differently that the Mage_Core_Model_Abstract/CRUD collections. The CRUD collections will have a SQL query assigned immediately at instantiation. The reporting collection, however, initializes its SQL statement during its load operation. You can see this in the load method down in the abstract base class for all reporting collections.

#File: app/code/core/Mage/Reports/Model/Resource/Report/Collection/Abstract.php
public function load($printQuery = false, $logQuery = false)
{
    if ($this->isLoaded()) {
        return $this;
    }
    $this->_initSelect();
    if ($this->_applyFilters) {
        $this->_applyDateRangeFilter();
        $this->_applyStoresFilter();
        $this->_applyCustomFilter();
    }
    return parent::load($printQuery, $logQuery);
}

There's no select until _initSelect is called.

The general approach you'll want to take here is

Create a new resource model class which extends the Mage_Reports_Model_Resource_Report_Collection_Abstract class
Define a _applyCustomFilter method in your class

and then use your new resource model. Your _applyCustomFilter method would look something like this.

protected function _applyCustomFilter()
{
    $return = parent::_applyCustomFilter();
    //manipulate the select all you want here (`$this->getSelect()`)
    return $return;
}

Magento 1.7 – Add Custom Fields to sales_flat_order

Your presumption is correct.
But for the sales_ table they are one and the same.
Initially the sales entities were EAV. They changed into flat tables starting with version 1.4.0.1 (I think). And for backwards compatibility both methods were kept.
For any other flat tabled entities (cms pages, blocks, polls) you cannot use addAttribute, only addColumn, but for sales it works both ways.
If you plan to make an extension that has to be compatible with versions before 1.4 then use addAttribute, otherwise I see no point in it.

Best Answer

Related Solutions

Magento – Joining to a Resource Model that doesn’t have a Select

Magento 1.7 – Add Custom Fields to sales_flat_order

Related Topic