Magento – Join Customer Address custom attribute to Order Address Collection

collection;custom-attributescustomer-addresssql

I would like to join custom customer/address attributes with the sales_flat_order_address table and sales/order_address_collection resource model.

The attributes are called gmap_lng and gmap_lat, and I set them via:

$address = Mage::getModel('customer/address');
$address->setGmapLng('value');
$address->setGmapLat('value');
$address->save();

I would like to join these attribute values in the sales/order_address_collection where there are matching customer_id values, but I'm not sure how to use join():

$orderAddresses = Mage::getResourceModel('sales/order_address_collection');
$orderAddresses->getSelect()->join(??,??,??);

I have gmap_lng and gmap_lat columns ready to be filled in sales_flat_order_address.

Thanks!

Best Answer

Once you call getSelect on a collection, you will be working with a Zend_Db_Select object. You can read about it here:

http://framework.zend.com/manual/1.12/en/zend.db.select.html#zend.db.select.building.join

Now regarding your question, you didn't mention how you've installed the gmap_lat and gmap_lng attributes. I'm assuming you've done so as an EAV attribute. As such, EAV makes it hard for us to "simply" join with the sales_flat_order_address info. Here's what it takes:

$orderAddresses = Mage::getResourceModel('sales/order_address_collection');
$read           = Mage::getSingleton('core/resource')->getConnection('core_read');
$gmapLat        = Mage::getModel('eav/entity_attribute')->loadByCode('customer_address', 'gmap_lat'); 
$gmapLng        = Mage::getModel('eav/entity_attribute')->loadByCode('customer_address', 'gmap_lng'); 

$orderAddresses->getSelect()
    ->joinLeft(
        array('caev_gmap_lat' => 'customer_address_entity_varchar'),
        '(caev_gmap_lat.entity_id = main_table.customer_address_id) AND ' . 
        '(' . $read->quoteInto('caev_gmap_lat.attribute_id = ?', $attribute->getId()) . ')',
        array('gmap_lat' => 'caev_gmap_lat.value')
    )
    ->joinLeft(
        array('caev_gmap_lng' => 'customer_address_entity_varchar'),
        '(caev_gmap_lng.entity_id = main_table.entity_id) AND ' . 
        '(' . $read->quoteInto('caev_gmap_lng.attribute_id = ?', $attribute->getId()) . ')',
        array('gmap_lat' => 'caev_gmap_lng.value')
    );

Here's a breakdown of what it does:

Get the sales order address collection
Get an instance of a DB read connection
Load the gmap_lat and gmap_lng attribute models
Join them with the address collection

Fetching the attribute models allows you to consistently get the right attribute ID, which you'll need to left join the data to the main table.

Note that I'm assuming your attributes were created as varchar in type. Adapt the tables if you store them as another type.

Related Solutions

Magento – Custom Module to Add Third Customer Address Type

First of all I want to say that the address_type is not used for customer addresses. It is used only for quote and order addresses, so there is no need to use that in order to flag your own address type.
if you need to retrieve only your store finder addresses I have an idea, but later on that.
First, you will need to create a new module to handle your new address type in order not to change the core code.
Let's call this module StackExchange_Storefinder.
You will need the following files:

app/etc/modules/StackExchange_Storefinder.xml - the declaration file:

<?xml version="1.0"?>
<config>
    <modules>
        <StackExchange_Storefinder>
            <codePool>local</codePool>
            <active>true</active>
            <depends>
                <Mage_Customer />
                <Mage_Adminhtml />
            </depends>
        </StackExchange_Storefinder>
    </modules>
</config>

app/code/local/StackExchange_Storefinder/etc/config.xml - the configuration file

<?xml version="1.0"?>
<config>
    <modules>
        <StackExchange_Storefinder>
            <version>1.0.0</version>
        </StackExchange_Storefinder>
    </modules>
    <global>
        <resources>
            <stackexchange_storefinder_setup>
                <setup>
                    <module>StackExchange_Storefinder</module>
                    <class>Mage_Customer_Model_Resource_Setup</class>
                </setup>
            </stackexchange_storefinder_setup>
        </resources>
        <models>
            <stackexchange_storefinder>
                <class>StackExchange_Storefinder_Model</class>
            </stackexchange_storefinder>
        </models>
        <helpers>
            <stackexchange_storefinder>
                <class>StackExchange_Storefinder_Helper</class>
            </stackexchange_storefinder>
        </helpers>
        <blocks>
            <stackexchange_storefinder>
                <class>StackExchange_Storefinder_Block</class>
            </stackexchange_storefinder>
            <adminhtml>
                <rewrite>
                    <customer_edit_tab_addresses>Stackexchange_Storefinder_Block_Adminhtml_Customer_Edit_Tab_Addresses</customer_edit_tab_addresses>
                </rewrite>
            </adminhtml>
        </blocks>
    </global>
    <adminhtml>
        <events>
            <adminhtml_customer_prepare_save>
                <observers>
                    <stackexchange_storefinder>
                        <class>stackexchange_storefinder/adminhtml_observer</class>
                        <method>checkStoreFinderAddress</method>
                    </stackexchange_storefinder>
                </observers>
            </adminhtml_customer_prepare_save>
        </events>
    </adminhtml>

</config>

app/code/local/StackExchange/Storefinder/sql/stackexchange_storefinder_setup/install-1.0.0.php - the install script that adds a new customer attribute to store the store finder address id (just like billing and shipping).

<?php
/** @var Mage_Customer_Model_Resource_Setup $this */
$this->addAttribute(
    'customer',
    'default_store_finder_id',
    array(
        'type'               => 'int',
        'label'              => 'Default Store Finder Address',
        'input'              => 'text',
        'backend'            => 'stackexchange_storefinder/customer_attribute_backend_storefinder',
        'required'           => false,
        'sort_order'         => 82,
        'visible'            => false,
    )
);

app/code/local/StackExchange/Storefinder/Model/Customer/Attribute/Backend/Storefinder.php - the backend model of the store finder attribute:

<?php
class StackExchange_Storefinder_Model_Customer_Attribute_Backend_Storefinder extends Mage_Eav_Model_Entity_Attribute_Backend_Abstract
{
    public function beforeSave($object)
    {
        $helper = $this->_getHelper();
        $defaultStoreFinder = $helper->getDefaultStorefinder($object);
        if (is_null($defaultStoreFinder)) {
            $object->setData('default_store_finder_id', null);
        }
    }
    public function afterSave($object)
    {
        if ($defaultStoreFinder = $this->_getHelper()->getDefaultStorefinder($object))
        {
            $addressId = false;
            /**
             * post_index set in customer save action for address
             * this is $_POST array index for address
             */
            foreach ($object->getAddresses() as $address) {
                if ($address->getPostIndex() == $defaultStoreFinder) {
                    $addressId = $address->getId();
                }
            }
            if ($addressId) {
                $object->setDefaultStoreFinderId($addressId);
                $this->getAttribute()->getEntity()
                    ->saveAttribute($object, $this->getAttribute()->getAttributeCode());
            }
        }
    }

    /**
     * @return StackExchange_Storefinder_Helper_Address
     */
    protected function _getHelper()
    {
        return Mage::helper('stackexchange_storefinder/address');
    }
}

app/code/local/StackExchange/Storefinder/Helper/Address.php - a helper so you won't rewrite the customer model:

<?php
class StackExchange_Storefinder_Helper_Address extends Mage_Core_Helper_Abstract
{
    public function getDefaultStorefinder(Mage_Customer_Model_Customer $customer)
    {
        return $customer->getData('default_store_finder_id');
    }
    public function getDefaultStorefinderAddress(Mage_Customer_Model_Customer $customer)
    {
        return $customer->getPrimaryAddress($customer->getData('default_store_finder_id'));
    }
}

app/code/local/StackExchange/Storefinder/Block/Adminhtml/Customer/Edit/Tab/Addresses.php - rewrite the admin address block to add the new radio button

<?php
class Stackexchange_Storefinder_Block_Adminhtml_Customer_Edit_Tab_Addresses
    extends Mage_Adminhtml_Block_Customer_Edit_Tab_Addresses
{
    public function __construct()
    {
        parent::__construct();
        $this->setTemplate('stackexchange_storefinder/tab/addresses.phtml');
    }
}

app/design/adminhtml/default/default/template/stackexchange_storefinder/tab/addresses.phtml - the new address admin template. Same as the default one but with one extra radio button. I wrote this in a gist because it's too big to put in here

app/code/local/StackExchange/Storefinder/Model/Adminhtml/Observer.php - an observer for the customer save event so the new address flag will be recognized:

<?php
class StackExchange_Storefinder_Model_Adminhtml_Observer
{
    public function checkStoreFinderAddress($observer)
    {
        /** @var Mage_Customer_Model_Customer $customer */
        $customer = $observer->getCustomer();
        /** @var Mage_Core_Controller_Request_Http $request */
        $request = $observer->getRequest();
        $data = $request->getPost();
        if (isset($data['account']['default_storefinder'])) {
            $customer->setData('default_store_finder_id', $data['account']['default_storefinder']);
        }
    }
}

app/code/local/StackExchange/Storefinder/Helper/Data.php - the module default helper. Not sure this is needed.

<?php
class StackExchange_Storefinder_Helper_Data extends Mage_Core_Helper_Abstract
{

}

When you are done clear the cache.

Now, in order to get all addresses that have this new flag set, you first need to get all the customers and get the value of $customer->getDefaultStoreFinderId() and then get the address collection where the id is in the values collected above.

But from my point of view, these is a totally different question. First check if you can add a third address type by using the code above.

[EDIT]

To get all the addresses that are marked as store finder you need to do something like this:

$customerCollection = Mage::getModel('customer/customer')->getCollection()
     ->addAttributeToSelect('default_store_finder_id');
$addressIds = array();
foreach ($customerCollection as $customer) {
    $addressId = $customer->getDefaultStoreFinderId();
    if ($addressId) {
        $addressIds[] = $addressId;
    }
}

if (count($addressIds)) {
    $addresses = Mage::getModel('customer/address')->getCollection()
        ->addAttributeToSelect('*')
        ->addAttributeToFilter('entity_id', array('in' => $addressIds));
    //do what you need with $addresses.
}

Magento 1.9 Orders – Get Sales Order Attributes Collection

What you want to achieve is a little bit complex.

In the past Magentos quote, invoice, credit memo and shipping were EAV tables, but this changed I think from 1.5 to 1.6. So the orders doesn't have attributes anymore, as you can see, when you check the table eav_attribute. You still find an entry in eav_entity_type but no reference in the attribute table.

Therefore there is no way to get this automatically and the "frontend labels" are just translated strings.

Best Answer

Related Solutions

Magento – Custom Module to Add Third Customer Address Type

Magento 1.9 Orders – Get Sales Order Attributes Collection

Related Topic