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.
sales_flat_order_address
with itself?