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

CommentFileSizeAuthor
#8 uc_addresses.drush_.inc_.txt3.69 KBdgtlmoon
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

grub3’s picture

Now here is a second SQL query which creates default addresses:

INSERT INTO uc_addresses_defaults (uid, aid)
SELECT DISTINCT ON (uid) uid, aid FROM uc_addresses
WHERE uid NOT IN (SELECT uid FROM uc_addresses_defaults)

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!

Reichert-1’s picture

MySQL doesn't support the "SELECT DISTINCT ON" syntax. using "GROUP BY" accomplishes the same thing. MySQL versions of these are:

INSERT INTO uc_addresses (uid, first_name, last_name, phone, company, street1, street2, city, zone, postal_code, country, address_name, created, modified)

SELECT * 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
GROUP BY uid, last_name, street1, city
ORDER BY uid

... and ...

INSERT INTO uc_addresses_defaults (uid, aid)
SELECT uid, aid FROM uc_addresses
WHERE uid NOT IN (SELECT uid FROM uc_addresses_defaults)
GROUP BY uid

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".

mandreato’s picture

This is the query I used to migrate addresses after installing Ubercart Addresses on an environment with old users/orders:

        insert into uc_addresses (uid, first_name, last_name, phone, company, street1,
                                  street2, city, zone, postal_code, country,
                                -- address_name,
                                  default_shipping, default_billing, created, modified)
        select uid
              ,delivery_first_name first_name
              ,delivery_last_name last_name
              ,delivery_phone phone
              ,delivery_company company
              ,delivery_street1 street1
              ,delivery_street2 street2
              ,delivery_city city
              ,delivery_zone zone
              ,delivery_postal_code postal_code
              ,delivery_country country
              -- address_name
              ,1 default_shipping
              ,1 default_billing
              ,created
              ,modified
          from uc_orders UCO
         where order_id = (select max(order_id)
                             from uc_orders
                            where uid = UCO.uid)

Hope This Helps !

BarisW’s picture

Thanks 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?

MegaChriz’s picture

I 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:

  1. Navigate to the address settings page and click on tab "Migrate" (admin/store/settings/addresses/migrate).
  2. A form with several options are shown, for example:
    • Migrate all addresses
    • Migrate addresses from last orders only
    • Skip users that already have addresses in the address book
  3. Set the desired options and click on the "Migrate" button.

Volunteers are welcome to come with a elaborated proposal.

dgtlmoon’s picture

Version: 6.x-1.x-dev » 7.x-1.x-dev

For 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)

MegaChriz’s picture

Status: Needs review » Needs work

@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:

  • Should there come an extra config page?
  • Should there be a button on the order edit screen?
  • Should addresses from all orders be copied to address books or only from the customer's latest orders?
  • Should the feature be implemented as an operation for Views Bulk Operations?

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.

dgtlmoon’s picture

I'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

select aid, uid,count(*) as c from uc_addresses group by uid,address_name order by c;
dgtlmoon’s picture

Note: I had about 7500 addresses/orders migrated with that script in about 1 minute

MegaChriz’s picture

Ideally, 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.

esquareddesign’s picture

I used the transfer SQL script and addresses transferred but are still not listed under the users address book.