1

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!

1
  • what tables do you want to join? as I understood from the question: sales_flat_order_address with itself?
    – alex_b
    Oct 30, 2014 at 19:06

1 Answer 1

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.

Your Answer

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

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