Join Customer Address custom attribute to Order Address Collection

Question

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!

Answer 1

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.