Dear Friends,
I simply need to migrate +1000 existing addresses from uc_orders to uc_addresses.
I looked at the modules proposing migration, but this was too complicated for me and it relies on PHP.
Here is an SQL query which works "out of the box":
INSERT INTO uc_addresses (uid, first_name, last_name, phone, company, street1, street2, city, "zone", postal_code, country, address_name, created, modified)
SELECT DISTINCT ON (uid, last_name, street1, city) * FROM
(
SELECT
uid ,
delivery_first_name as first_name,
delivery_last_name as last_name,
delivery_phone as phone,
delivery_company as company,
delivery_street1 as street1,
delivery_street2 as street2,
delivery_city as city,
delivery_zone as "zone",
delivery_postal_code as postal_code,
delivery_country as country,
'Delivery' as address_name,
created,
modified
FROM uc_orders as import
WHERE delivery_last_name is not null
AND uid > 0
AND uid NOT IN (SELECT uid from uc_addresses)
UNION
SELECT
uid ,
billing_first_name as first_name,
billing_last_name as last_name,
billing_phone as phone,
billing_company as company,
billing_street1 as street1,
billing_street2 as street2,
billing_city as city,
billing_zone as "zone",
billing_postal_code as postal_code,
billing_country as country,
'Billing' as address_name,
created,
modified
FROM uc_orders as import
WHERE delivery_last_name is not null
AND uid > 0
AND uid NOT IN (SELECT uid from uc_addresses)
) as result
ORDER BY uid
The query will create one or several distinct addresses per user.
Please note:
* If a user already has addresses in uc_addresses, these addresses remain untouched. This is only for migrating addresses from uc_orders which are NOT in uc_addresses. In some cases, addresses may be old and this is your work to review them after import.
* When there is a single address, the name "Delivery" or "Billing" is unpredictable. But this is always the case in SQL when manipulating DISCTINCT values in order to remove duplicated.
* When there are two different addresses, the first address is the delivery, the second is the billing.
* This SQL was tested under PostgreSQL. MySQL is untested, because we have a ban on MySQL in our company and we only support DB2, Oracle and PostgreSQL.
I would appreciate if you could test this query and try to improve it. Maybe make it an option in the preferences to allow migration of data.
Kind regards,
Jean-Michel
Comment | File | Size | Author |
---|---|---|---|
#8 | uc_addresses.drush_.inc_.txt | 3.69 KB | dgtlmoon |
Comments
Comment #1
grub3 CreditAttribution: grub3 commentedNow here is a second SQL query which creates default addresses:
Default values in uc_addresses_defaults are left untouched.
So there is not risk to create duplicates.
When there is one address, the single address becomes default.
When there are several addresses, one of them becomes default by random (again, this is the nature of SQL DISTINCT and there is nothing we can do to my knowledge).
Enjoy!
Comment #2
Reichert-1 CreditAttribution: Reichert-1 commentedMySQL doesn't support the "SELECT DISTINCT ON" syntax. using "GROUP BY" accomplishes the same thing. MySQL versions of these are:
... and ...
I tested both of these on my own production database successfully.
UPDATE: Had to make a minor correction to the second query - should be "GROUP BY uid" not "GROUP BY uid, aid".
Comment #3
mandreato CreditAttribution: mandreato commentedThis is the query I used to migrate addresses after installing Ubercart Addresses on an environment with old users/orders:
Hope This Helps !
Comment #4
BarisW CreditAttribution: BarisW commentedThanks for sharing. Wouldn't it be nice to add these queries to the uc_addresses.install file so that addresses are migrated by default when enabling the module?
Comment #5
MegaChriz CreditAttribution: MegaChriz commentedI don't think addresses should be migrated automatically when installing the module. It should be an optional feature.
The first step for adding this feature is by making a proposal on how it should work, for example:
Volunteers are welcome to come with a elaborated proposal.
Comment #6
dgtlmoon CreditAttribution: dgtlmoon commentedFor some reason I thought this module would take care of this for me, sounds like some really handy functionality (I have a large/popular site and wanted to let people update their addresses through their user/%uid page, and found this module)
Comment #7
MegaChriz CreditAttribution: MegaChriz commented@dgtlmoon
The main purpose for this module is providing an address book for the customer from which he/she can select an address during checkout.
A feature to migrate addresses from orders to the customer's address book would be nice to have, though I don't need it myself. Patches that add this feature are welcome, though it would probably be better to first discuss about how this feature should be designed. Some things to think about, for example:
Note that since the 7.x-1.x version has Feeds integration (the 6.x-2.x version does not have this), you could create a View of Ubercart Orders, export the Order addresses to CSV (with Views data export) and import them again using Feeds. It's a bit devious, but it could be a good alternative.
Comment #8
dgtlmoon CreditAttribution: dgtlmoon commentedI'm sort of in a hurry for a client, so I had a go at doing this as a drush command.
I really don't trust anything todo with Feeds module unfortunately, and those extra steps do sound very devious indeed ;)
Attached is my drush script, I had to alter the length for "address_name" because this sets the "address_name" based on the street/city/country entry (Suggestions welcome to change this, but this seems to need to be unique across the whole table? #2034847: Column "address_name" should have a unique key requirement )
Apologies for slack filename of the attachment, it's not a patch just yet - should be uc_addresses.drush.inc
Once the drush command is run, I check that there is only 1 "address_name" available by doing
Comment #9
dgtlmoon CreditAttribution: dgtlmoon commentedNote: I had about 7500 addresses/orders migrated with that script in about 1 minute
Comment #10
MegaChriz CreditAttribution: MegaChriz commentedIdeally, instead of using
db_insert()
on the uc_addresses table, consider to use the the address book API to insert the addresses. The API has some functionality to check for double addresses and makes sure that only one address will be marked as default shipping/billing per customer.Comment #11
esquareddesign CreditAttribution: esquareddesign commentedI used the transfer SQL script and addresses transferred but are still not listed under the users address book.