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

3
  • 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
    Commented Feb 18, 2019 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
    Commented Feb 18, 2019 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 :/ Commented Mar 26, 2019 at 16:11

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.