2

I wanted to obtain all the information related to an specific customer (given their account number)

I mean, i want to obtain name, lastname, email, billing/shipping addresses(including the ORIG_SYSTEM_REFERENCE for each one), city, region, country and so on.

I have tried different queries but no one really helped 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_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

This is the MAGENTO database diagram:

Can help me with this?

image

  • Why are you using direct query instead of models? you could get all information you need by using customer model and customer address model – NID Feb 18 at 4:57
  • your statement returns all your customers with all their addresses stored in your system, so if a customer has two addresses you will get two rows for this customer. In my system your query works well. So far I can't understand what your question is :-) . Regarding ORIG_SYSTEM_REFERENCE: I don't know what you mean with that, is that a custom attribute / field in your database? – HelgeB Feb 18 at 7:19
  • Dude, check this: magento.stackexchange.com/questions/260305/… I want to obtain only those items that are enabled.. i've tried so many times without success :(.. i dont know how to add the condition :/ – programming_amazing Mar 26 at 16:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.