Getting all customer addresses which have empty required fields

Question

I'm looking for a proper way to get all customer addresses that have empty required fields (eg. postcode, street,...) so i can later delete those. (This is due to a previous version in which empty addresses were made as a placeholder by former developers. This functionality has now been removed, but those addresses still remain, causing frustration to the users who want to use one of those addresses in checkout, getting a notification that certain data is required.)

I'm fairly new to Magento, but I know there is a way you can do this in code and call the ->getSelect() to ouput a query which I can use to output the data to csv and to later remove all those addresses in a similar fashion.

So basically there are 2 things that need to happen:

Get all required address fields
Get all customer addresses which have empty data in any of these fields

(Later on I intend to use this same function to delete those addresses, but I'm sure I can figure it out once I have the needed addresses.)

I have little experience with Magento's query language, but this seems challenging even to more experienced Magento developers, so if you can get me on my way with this, I would be extremely grateful.

Are you going to delete the customer details from the database directly using query? or going to write code in magento to handle this? — seeni, Commented Feb 16, 2017 at 9:14
Since it's a one time operation, I think it would be easier to input the query into the database manually. — Nico V, Commented Feb 16, 2017 at 9:18

Community · Accepted Answer · 2020-06-15 08:30:17Z

Following are the queries you can use,

Run the below query to get the required address attribute id values,

select * from eav_attribute where entity_type_id=2 and is_required=1;

Use the attribute_id from the last query, and use it in the corresponding backend_type tables,

query 1 >> select entity_id from customer_address_entity_varchar where attribute_id in (20,22,26,27,30,31) and value is NULL;

query 2 >> select entity_id from customer_address_entity_text where attribute_id in (25) and value is NULL;

This queries will give the customer entity_id values who are all have empty required field values.

Use the comma separated entity_id values in the following delete quires,

delete from customer_address_entity_varchar where entity_id in (>result from query 1<) and attribute_id in (20,22,26,27,30,31);

delete from customer_address_entity_text where entity_id in (>result from query 2<) and attribute_id in (25);

These delete quires will delete the empty required address fields of the customer. To delete all the address fields for the customer you can pick the attribute_id from eav_attribute using,

select * from eav_attribute where entity_type_id=2;

I don't just want to delete the fields, I want to completely delete the address which contains empty required fields. How would I do that? — Nico V, Commented Feb 16, 2017 at 13:24
delete the rows from the following table, delete from customer_address_entity where parent_id in (<values>); parent_id is the customer id value — seeni, Commented Feb 16, 2017 at 13:29

Nico V · Accepted Answer · 2017-05-05 11:35:36Z

Edit: Original answer follows here, for the actual queries used, look below at Update

Original answer

For anyone stumbling across the same (or similar) problem in the future. Here's what worked for me. After some more searching and trial and error, I've found following query to give me the results that I wanted:

SELECT caev.value FirstName, caev2.value LastName, caet.value Address, caev3.value City, caev5.value State, caev6.value Postcode, caev4.value Country, caev7.value Tel FROM customer_entity ce
JOIN customer_address_entity cae ON cae.parent_id = ce.entity_id
LEFT JOIN customer_address_entity_varchar caev ON caev.entity_id = cae.entity_id AND caev.attribute_id = 20
LEFT JOIN customer_address_entity_varchar caev2 ON caev2.entity_id = cae.entity_id AND caev2.attribute_id = 22
LEFT JOIN customer_address_entity_text caet ON caet.entity_id = cae.entity_id AND caet.attribute_id = 25
LEFT JOIN customer_address_entity_varchar caev3 ON caev3.entity_id = cae.entity_id AND caev3.attribute_id = 26
LEFT JOIN customer_address_entity_varchar caev4 ON caev4.entity_id = cae.entity_id AND caev4.attribute_id = 27
LEFT JOIN customer_address_entity_varchar caev5 ON caev5.entity_id = cae.entity_id AND caev7.attribute_id = 28
LEFT JOIN customer_address_entity_varchar caev6 ON caev6.entity_id = cae.entity_id AND caev5.attribute_id = 30
LEFT JOIN customer_address_entity_varchar caev7 ON caev7.entity_id = cae.entity_id AND caev6.attribute_id = 31
HAVING FirstName IS NULL OR LastName IS NULL OR Address = '' OR City IS NULL OR Postcode IS NULL OR Country IS NULL;

The attribute id's of required fields I got from following query:

SELECT * FROM `eav_attribute` WHERE `entity_type_id` = 2 AND `is_required` = 1 ORDER BY `attribute_id` ASC;

I've also added some non-required attributes to display a more detailed result for exporting to csv.

Update:

Here are the exacte queries I used to delete all customer addresses with empty required fields.

Query 1 to get all required fields:

SELECT * FROM eav_attribute WHERE entity_type_id = 2 AND is_required = 1;

(as provided by seeni) This got me the following attribute_id's: 20 (firstname),22 (lastname),25 (street),26 (city),27 (country_id),28 (region),30 (postcode),31 (telephone)

Query 2 to get a list of all addresses which have empty required (just to get a visual representatation of all incomplete addresses):

SELECT ce.entity_id, cae.entity_id AdresId, caev.value Voornaam, caev2.value Achternaam, caet.value Adres, caev3.value Gemeente, caev5.value Provincie, caev6.value Postcode, caev4.value Land, caev7.value Tel FROM customer_entity ce
JOIN customer_address_entity cae ON cae.parent_id = ce.entity_id
LEFT JOIN customer_address_entity_varchar caev ON caev.entity_id = cae.entity_id AND caev.attribute_id = 20
LEFT JOIN customer_address_entity_varchar caev2 ON caev2.entity_id = cae.entity_id AND caev2.attribute_id = 22
LEFT JOIN customer_address_entity_text caet ON caet.entity_id = cae.entity_id AND caet.attribute_id = 25
LEFT JOIN customer_address_entity_varchar caev3 ON caev3.entity_id = cae.entity_id AND caev3.attribute_id = 26
LEFT JOIN customer_address_entity_varchar caev4 ON caev4.entity_id = cae.entity_id AND caev4.attribute_id = 27
LEFT JOIN customer_address_entity_varchar caev5 ON caev5.entity_id = cae.entity_id AND caev5.attribute_id = 28
LEFT JOIN customer_address_entity_varchar caev6 ON caev6.entity_id = cae.entity_id AND caev6.attribute_id = 30
LEFT JOIN customer_address_entity_varchar caev7 ON caev7.entity_id = cae.entity_id AND caev7.attribute_id = 31
HAVING Voornaam IS NULL
OR Voornaam = '' 
OR Achternaam IS NULL
OR Achternaam = ''
OR Adres IS NULL
OR Adres = '' 
OR Gemeente IS NULL
OR Gemeente = ''
OR Postcode IS NULL
OR Postcode = ''
OR Land IS NULL
OR Land = ''
OR Tel IS NULL
OR Tel = ''

Query 3 to delete all empty varchar entries:

DELETE FROM customer_address_entity_varchar WHERE entity_id IN (SELECT AdresId FROM (SELECT ce.entity_id, cae.entity_id AdresId, caev.value Voornaam, caev2.value Achternaam, caet.value Adres, caev3.value Gemeente, caev5.value Provincie, caev6.value Postcode, caev4.value Land, caev7.value Tel FROM customer_entity ce
JOIN customer_address_entity cae ON cae.parent_id = ce.entity_id
LEFT JOIN customer_address_entity_varchar caev ON caev.entity_id = cae.entity_id AND caev.attribute_id = 20
LEFT JOIN customer_address_entity_varchar caev2 ON caev2.entity_id = cae.entity_id AND caev2.attribute_id = 22
LEFT JOIN customer_address_entity_text caet ON caet.entity_id = cae.entity_id AND caet.attribute_id = 25
LEFT JOIN customer_address_entity_varchar caev3 ON caev3.entity_id = cae.entity_id AND caev3.attribute_id = 26
LEFT JOIN customer_address_entity_varchar caev4 ON caev4.entity_id = cae.entity_id AND caev4.attribute_id = 27
LEFT JOIN customer_address_entity_varchar caev5 ON caev5.entity_id = cae.entity_id AND caev5.attribute_id = 28
LEFT JOIN customer_address_entity_varchar caev6 ON caev6.entity_id = cae.entity_id AND caev6.attribute_id = 30
LEFT JOIN customer_address_entity_varchar caev7 ON caev7.entity_id = cae.entity_id AND caev7.attribute_id = 31
HAVING Voornaam IS NULL
OR Voornaam = '' 
OR Achternaam IS NULL
OR Achternaam = ''
OR Adres IS NULL
OR Adres = '' 
OR Gemeente IS NULL
OR Gemeente = ''
OR Postcode IS NULL
OR Postcode = ''
OR Land IS NULL
OR Land = ''
OR Tel IS NULL
OR Tel = '') AS AddressId) AND attribute_id IN (20,22,26,27,28,30,31);

Query 4 to delete all empty text fields

DELETE FROM customer_address_entity_text WHERE entity_id IN (SELECT AdresId FROM (SELECT ce.entity_id, cae.entity_id AdresId, caev.value Voornaam, caev2.value Achternaam, caet.value Adres, caev3.value Gemeente, caev5.value Provincie, caev6.value Postcode, caev4.value Land, caev7.value Tel FROM customer_entity ce
JOIN customer_address_entity cae ON cae.parent_id = ce.entity_id
LEFT JOIN customer_address_entity_varchar caev ON caev.entity_id = cae.entity_id AND caev.attribute_id = 20
LEFT JOIN customer_address_entity_varchar caev2 ON caev2.entity_id = cae.entity_id AND caev2.attribute_id = 22
LEFT JOIN customer_address_entity_text caet ON caet.entity_id = cae.entity_id AND caet.attribute_id = 25
LEFT JOIN customer_address_entity_varchar caev3 ON caev3.entity_id = cae.entity_id AND caev3.attribute_id = 26
LEFT JOIN customer_address_entity_varchar caev4 ON caev4.entity_id = cae.entity_id AND caev4.attribute_id = 27
LEFT JOIN customer_address_entity_varchar caev5 ON caev5.entity_id = cae.entity_id AND caev5.attribute_id = 28
LEFT JOIN customer_address_entity_varchar caev6 ON caev6.entity_id = cae.entity_id AND caev6.attribute_id = 30
LEFT JOIN customer_address_entity_varchar caev7 ON caev7.entity_id = cae.entity_id AND caev7.attribute_id = 31
HAVING Voornaam IS NULL
OR Voornaam = '' 
OR Achternaam IS NULL
OR Achternaam = ''
OR Adres IS NULL
OR Adres = '' 
OR Gemeente IS NULL
OR Gemeente = ''
OR Postcode IS NULL
OR Postcode = ''
OR Land IS NULL
OR Land = ''
OR Tel IS NULL
OR Tel = '') AS AddressId) AND attribute_id IN (25);

Query 5 to delete the affected address entities:

DELETE FROM customer_address_entity WHERE entity_id IN (SELECT AdresId FROM (SELECT ce.entity_id, cae.entity_id AdresId, caev.value Voornaam, caev2.value Achternaam, caet.value Adres, caev3.value Gemeente, caev5.value Provincie, caev6.value Postcode, caev4.value Land, caev7.value Tel FROM customer_entity ce
JOIN customer_address_entity cae ON cae.parent_id = ce.entity_id
LEFT JOIN customer_address_entity_varchar caev ON caev.entity_id = cae.entity_id AND caev.attribute_id = 20
LEFT JOIN customer_address_entity_varchar caev2 ON caev2.entity_id = cae.entity_id AND caev2.attribute_id = 22
LEFT JOIN customer_address_entity_text caet ON caet.entity_id = cae.entity_id AND caet.attribute_id = 25
LEFT JOIN customer_address_entity_varchar caev3 ON caev3.entity_id = cae.entity_id AND caev3.attribute_id = 26
LEFT JOIN customer_address_entity_varchar caev4 ON caev4.entity_id = cae.entity_id AND caev4.attribute_id = 27
LEFT JOIN customer_address_entity_varchar caev5 ON caev5.entity_id = cae.entity_id AND caev5.attribute_id = 28
LEFT JOIN customer_address_entity_varchar caev6 ON caev6.entity_id = cae.entity_id AND caev6.attribute_id = 30
LEFT JOIN customer_address_entity_varchar caev7 ON caev7.entity_id = cae.entity_id AND caev7.attribute_id = 31
HAVING Voornaam IS NULL
OR Voornaam = '' 
OR Achternaam IS NULL
OR Achternaam = ''
OR Adres IS NULL
OR Adres = '' 
OR Gemeente IS NULL
OR Gemeente = ''
OR Postcode IS NULL
OR Postcode = ''
OR Land IS NULL
OR Land = ''
OR Tel IS NULL
OR Tel = '') AS AddressId)

Hopefully this will be helpfull to some in the future, as it took me quite a lot of trail and error to get this right.

Stack Exchange Network

Getting all customer addresses which have empty required fields

2 Answers 2

Original answer

Update:

Your Answer

Not the answer you're looking for? Browse other questions tagged
database
customer-address
query
or ask your own question.

Hot Network Questions

Getting all customer addresses which have empty required fields

2 Answers 2

Original answer

Update:

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged databasecustomer-addressquery or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
database
customer-address
query
or ask your own question.