Magento 1.9 – How to Join Customer Collection with Customer Address

customer-addressmagento-1.9modulePHPquery

In Magento i want to search the customer with the address(state,city,zipcode), can you explain to me how to the achieve that.

The customer collection query

$collection = Mage::getModel('customer/customer')->getCollection();
$collection->addAttributeToSelect(array('userid','firstname','lastname','profile_image','specialist','latitude','free_consult','longitude'));
$collection->addAttributeToFilter('group_id',Federallawyer_Customer_Helper_Data::LAWYER);

also my shipping and billing address is same.

Best Answer

$collection = Mage::getModel('customer/customer')->getCollection()
            ->joinAttribute('billing_postcode', 'customer_address/postcode', 'default_billing', null, 'left')
            ->joinAttribute('billing_city', 'customer_address/city', 'default_billing', null, 'left')
            ->joinAttribute('billing_region', 'customer_address/region', 'default_billing', null, 'left')
            ->addFieldToFilter('billing_postcode','123456') // replace with your post code
            ->addFieldToFilter('billing_city','test') // replace with your city
            ->addFieldToFilter('billing_region','test') // replace with your region

Related Solutions

Magento – Join Customer Address custom attribute to Order Address Collection

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.

collection – Subselect as main_table on Magento Collection

You can try something like:

$subQuery = new Zend_Db_Select();

...

$collection->getSelect()->from($subQuery);

The customer collection query

Best Answer

Related Solutions

Magento – Join Customer Address custom attribute to Order Address Collection

collection – Subselect as main_table on Magento Collection

Related Topic