SQL query to get all addresses associated with customer in Magento

Question

I'm migrating from Magento, and need the mySQL query to get all addresses associated with each customer.

this is how I get the address IDs, but not sure if customer_address_entity.parent_id is a customer id.

select 
    email, group_concat(a.entity_id)
from
    customer_entity as c
        inner join
    customer_address_entity as a ON a.parent_id = c.entity_id
group by email

My Magento DB dump is from Magento 1.6.x

Can you propose a query for it?

Answer 1

I found it. Just check if in your database the entity_id is the same as in my example.

In my dump they are:

city=15, country_id=11, firstname=9, lastname=10, postcode=14, street=16, telephone=17, region=12,region_id=13

And here is the query which worked for me.

SELECT
    email,
    a.entity_id AS addressId,
    IF(def_billing_address.value = a.entity_id,1,0) AS isDefaultBillingAddress,
    IF(def_shipping_address.value = a.entity_id,1,0) AS isDefaultSippingAddress,
    addr_street.value AS street,
    addr_city.value AS city,
    addr_region_code.code AS stateCode,
    addr_region.value AS state,
    addr_zipcode.value AS postalCode,
    addr_country.value AS countryCode

FROM
    customer_entity AS c
        INNER JOIN
    customer_address_entity AS a ON a.parent_id = c.entity_id

-- DEFAULT BILLING ADDRESS - 7
-- DEFAULT SHIPPING ADDRESS - 8
-- city=15, country_id=11, firstname=9, lastname=10, postcode=14, street=16, telephone=17, region=12,region_id=13
LEFT JOIN customer_entity_int AS def_billing_address ON
  (def_billing_address.entity_id = c.entity_id) AND
  (def_billing_address.attribute_id = 7)
LEFT JOIN customer_entity_int AS def_shipping_address ON
  (def_shipping_address.entity_id = c.entity_id) AND
  (def_shipping_address.attribute_id = 8)
LEFT JOIN customer_address_entity_varchar AS addr_zipcode ON
    a.entity_id = addr_zipcode.entity_id AND
    addr_zipcode.attribute_id = 14
LEFT JOIN customer_address_entity_varchar AS addr_city ON
    a.entity_id = addr_city.entity_id AND
    addr_city.attribute_id = 15
LEFT JOIN customer_address_entity_varchar AS addr_country ON
    a.entity_id = addr_country.entity_id AND
    addr_country.attribute_id = 11
LEFT JOIN customer_address_entity_varchar AS addr_firstname ON
    a.entity_id = addr_firstname.entity_id AND
    addr_firstname.attribute_id = 9
LEFT JOIN customer_address_entity_varchar AS addr_lastname ON
    a.entity_id = addr_lastname.entity_id AND
    addr_lastname.attribute_id = 10
LEFT JOIN customer_address_entity_text AS addr_street ON
    a.entity_id = addr_street.entity_id AND
    addr_street.attribute_id = 16
LEFT JOIN customer_address_entity_varchar AS addr_telephone ON
    a.entity_id = addr_telephone.entity_id AND
    addr_telephone.attribute_id = 17
LEFT JOIN customer_address_entity_varchar AS addr_region ON
    a.entity_id = addr_region.entity_id AND
    addr_region.attribute_id = 12
LEFT JOIN customer_address_entity_int AS addr_region_id ON
    a.entity_id = addr_region_id.entity_id AND
    addr_region_id.attribute_id = 13
LEFT JOIN directory_country_region AS addr_region_code ON
    addr_region_id.value = addr_region_code.region_id

Answer 2

Better SQL then above.. will auto get IDs.. just need to pass entity Type ID where 1 = customer Entity Type and 2 = customer address entity type.. additionally could find those 2 values out via code

$customerTypeID = Mage::getModel('eav/entity')->setType('customer')->getTypeId();
$customerAddressTypeID = Mage::getModel('eav/entity')->setType('customer_address')->getTypeId();

AND BELOW SQL :

SELECT
email,
a.entity_id AS addressId,
IF(def_billing_address.value = a.entity_id,1,0) AS isDefaultBillingAddress,
IF(def_shipping_address.value = a.entity_id,1,0) AS isDefaultSippingAddress,
addr_firstname.value AS firstname,
addr_lastname.value AS lastname,
addr_street.value AS street,
addr_city.value AS city,
addr_region_code.code AS stateCode,
addr_region.value AS state,
addr_zipcode.value AS postalCode,
addr_country.value AS countryCode,
addr_telephone.value AS telephone
FROM mg_customer_entity AS c
INNER JOIN mg_customer_address_entity AS a ON a.parent_id = c.entity_id

LEFT JOIN mg_customer_entity_int AS def_billing_address ON
    (def_billing_address.entity_id = c.entity_id) AND
    (def_billing_address.attribute_id = (SELECT attribute_id FROM mg_eav_attribute AS eav WHERE eav.attribute_code = 'default_billing' and eav.entity_type_id = 1))
LEFT JOIN mg_customer_entity_int AS def_shipping_address ON
    (def_shipping_address.entity_id = c.entity_id) AND
    (def_shipping_address.attribute_id = (SELECT attribute_id FROM mg_eav_attribute AS eav WHERE eav.attribute_code = 'default_shipping' and eav.entity_type_id = 1))
LEFT JOIN mg_customer_address_entity_varchar AS addr_zipcode ON
    a.entity_id = addr_zipcode.entity_id AND
    addr_zipcode.attribute_id = (SELECT attribute_id FROM mg_eav_attribute AS eav WHERE eav.attribute_code = 'postcode' and eav.entity_type_id = 2)
LEFT JOIN mg_customer_address_entity_varchar AS addr_city ON
    a.entity_id = addr_city.entity_id AND
    addr_city.attribute_id = (SELECT attribute_id FROM mg_eav_attribute AS eav WHERE eav.attribute_code = 'city' and eav.entity_type_id = 2)
LEFT JOIN mg_customer_address_entity_varchar AS addr_country ON
    a.entity_id = addr_country.entity_id AND
    addr_country.attribute_id = (SELECT attribute_id FROM mg_eav_attribute AS eav WHERE eav.attribute_code = 'country_id' and eav.entity_type_id = 2)
LEFT JOIN mg_customer_address_entity_varchar AS addr_firstname ON
    a.entity_id = addr_firstname.entity_id AND
    addr_firstname.attribute_id = (SELECT attribute_id FROM mg_eav_attribute AS eav WHERE eav.attribute_code = 'firstname' and eav.entity_type_id = 2)
LEFT JOIN mg_customer_address_entity_varchar AS addr_lastname ON
    a.entity_id = addr_lastname.entity_id AND
    addr_lastname.attribute_id = (SELECT attribute_id FROM mg_eav_attribute AS eav WHERE eav.attribute_code = 'lastname' and eav.entity_type_id = 2)
LEFT JOIN mg_customer_address_entity_text AS addr_street ON
    a.entity_id = addr_street.entity_id AND
    addr_street.attribute_id = (SELECT attribute_id FROM mg_eav_attribute AS eav WHERE eav.attribute_code = 'street' and eav.entity_type_id = 2)
LEFT JOIN mg_customer_address_entity_varchar AS addr_telephone ON
    a.entity_id = addr_telephone.entity_id AND
    addr_telephone.attribute_id = (SELECT attribute_id FROM mg_eav_attribute AS eav WHERE eav.attribute_code = 'telephone' and eav.entity_type_id = 2)
LEFT JOIN mg_customer_address_entity_varchar AS addr_region ON
    a.entity_id = addr_region.entity_id AND
    addr_region.attribute_id = (SELECT attribute_id FROM mg_eav_attribute AS eav WHERE eav.attribute_code = 'region' and eav.entity_type_id = 2)
LEFT JOIN mg_customer_address_entity_int AS addr_region_id ON
    a.entity_id = addr_region_id.entity_id AND
    addr_region_id.attribute_id = (SELECT attribute_id FROM mg_eav_attribute AS eav WHERE eav.attribute_code = 'region_id' and eav.entity_type_id = 2)
LEFT JOIN mg_directory_country_region AS addr_region_code ON
    addr_region_id.value = addr_region_code.region_id