Retrieving default billing and shipping information in Magento

I recently got to play around with Magento (eCommerce platform based on Zend Framework) and had to figure out default billing and shipping information for all of our current clients.

Apparently developer community build around Magento is pretty amateurish judging by technical discussions that are found on their forums.

When I looked up how to retrieve default address information, the answers would all be in a pure PHP code that utilized Magento models to retrieve data.

Thats bloated and frankly plain dumb. Not to mention that some of us do not use PHP for system specific tasks.

In my case I’m using Python so I needed a SQL query that I can run against our Magento database to retrieve required data.

Magento stores data with dozens of relations (very confusing if you are just starting out) and some of relations do not make much sense. They split out entity properties by type (datetime, int, text, etc) and associate each type separately to main entity via id.

Not to mention that address data is considered to be it’s own entity and has it’s indexing outside customers space.

First we have to grab default billing and shipping ids associated to our user accounts:

SELECT 
    `ce`.`email`,
    `default_billing_id`.`value` AS `default_billing`,
    `default_shipping_id`.`value` AS `default_shipping`
FROM
    `customer_entity` AS `ce`
	LEFT JOIN `customer_entity_int` AS `default_billing_id` ON
		(`default_billing_id`.`entity_id` = `ce`.`entity_id`) AND
		(`default_billing_id`.`attribute_id` = '14')
	LEFT JOIN `customer_entity_int` AS `default_shipping_id` ON 
		(`default_shipping_id`.`entity_id` = `ce`.`entity_id`) AND
		(`default_shipping_id`.`attribute_id` = '13')
WHERE
    (`ce`.`entity_type_id` = 1) AND
    (`ce`.`is_active` = 1) 

As you can see attribute id of ‘13’ represents default shipping identifier and attribute ‘14’ represents default billing identifier.

Now we need to join across address entity to retrieve actual address information linked to default addresses the user has, in this example I will retrieve default zip code for both billing and shipping address:

SELECT 
    `ce`.`email`,
    `default_billing_id`.`value` AS `default_billing`,
    `default_shipping_id`.`value` AS `default_shipping`,
    `default_billing_zipcode`.`value` AS `default_billing_zipcode`,
    `default_shipping_zipcode`.`value` AS `default_shipping_zipcode`
FROM
    `customer_entity` AS `ce`
	LEFT JOIN `customer_entity_int` AS `default_billing_id` ON
		(`default_billing_id`.`entity_id` = `ce`.`entity_id`) AND
		(`default_billing_id`.`attribute_id` = '14')
	LEFT JOIN `customer_entity_int` AS `default_shipping_id` ON 
		(`default_shipping_id`.`entity_id` = `ce`.`entity_id`) AND
		(`default_shipping_id`.`attribute_id` = '13')
	LEFT JOIN `customer_address_entity_varchar` AS `default_shipping_zipcode` ON 
		(`default_shipping_id`.`value` = `default_shipping_zipcode`.`entity_id`) AND
		(`default_shipping_zipcode`.`attribute_id` = '29')
	LEFT JOIN `customer_address_entity_varchar` AS `default_billing_zipcode` ON 
		(`default_billing_id`.`value` = `default_billing_zipcode`.`entity_id`) AND
		(`default_billing_zipcode`.`attribute_id` = '29')
WHERE
    (`ce`.`entity_type_id` = 1) AND
    (`ce`.`is_active` = 1)

Note that attribute_id’s might vary in your installation. You might consider modifying this query to use attribute labels for a more portable approach.

Feel free to reach out to me if you have any questions.