Shopware 6: Fix migrated customers with multiple accounts across sales channels

Shopware: 6.5.6.1, but more important MySQL 8!

We migrated a Magento 1 store to Shopware. One of the downsides we discovered later is, that all customers were unrestricted about sales channels.

That is especially a problem, because some customers have more than one account and because all accounts are attached to all sales channel, they are unable to login, because Shopware can’t determine which account is the correct one.

Our plan of action to tackle this problem is:

1. Identify all customer/email which have more than one account
2. Check in which sales channel they made orders (should only be one)
3. And then attach the account to this one sales channel only

Get broken accounts

SELECT DISTINCT
	c1.email
FROM
	customer c1
	INNER JOIN customer c2 ON c1.email = c2.email
		AND c1.id <> c2.id
		AND c1.guest = 0
		AND c2.guest = 0

The first step is to get the emails. For this we query the database for all emails and join customer into it self, while we only connect accounts which have the same email, but not the same id and are not guests. This way we get a list of emails with all accounts.

Check where they bought stuff

SELECT
	c.id,
	c.email,
	GROUP_CONCAT(DISTINCT (HEX(o.sales_channel_id)) SEPARATOR ' '),
	LENGTH(GROUP_CONCAT(DISTINCT o.sales_channel_id))
FROM
	customer c
	INNER JOIN order_customer oc ON oc.customer_id = c.id
	INNER JOIN `order` o ON o.id = oc.order_id
WHERE
	c.guest = 0
	AND c.email IN
(
-- LONG LIST OF EMAILS
)
GROUP BY c.id, c.email

Next step was to get the sales channel of the customers’ order. We join us from the customer, via order_customer to the order where we find the sales_channel_id, this is the info we want to write into bound_sales_channel_id.

But because it is a production database and I’m not sixteen anymore and the customers does more than a couple of euros per day of revenue, we want to be careful – especially because my assumptions, that each customer only bought in one sales channel turned to be wrong. We have 14 exceptions ?.

Be pragmatic

We have over 100.000 customers, around 1.800 of them have multiple accounts, so loosing 14 of them isn’t that dramatic. The probability, that we loose a customer which actually comes back is not that high – and even if it is, customer care will take care of it and we might fix it later.

The alternative would be to invest a couple of hours, decide which sales channel to attach the customer to and what to do with the orders. This can be decided in the future – or maybe not ?.

Bound the customer to one sales channel

And because I had a ton of problems with You can't specify target table for update in FROM clause I tried with temptorary tables and because this didn’t work out, I learned something new today: Common table expressions (CTE).

The query to update everything looks now like this:

WITH cte AS
         (SELECT c.id               cid,
                 o.sales_channel_id scid
          FROM customer c
                   INNER JOIN order_customer oc ON oc.customer_id = c.id
                   INNER JOIN `order` o ON o.id = oc.order_id
          WHERE c.id IN (SELECT DISTINCT c1.id
                         FROM customer c1
                                  INNER JOIN customer c2 ON c1.email = c2.email
                             AND c1.id <> c2.id
                             AND c1.guest = 0
                             AND c2.guest = 0
                             AND c1.order_count > 0
                             AND c2.order_count > 0)
          GROUP BY c.id, o.sales_channel_id
          HAVING LENGTH(GROUP_CONCAT(DISTINCT o.sales_channel_id)) < 30)

UPDATE
    customer c_update
SET bound_sales_channel_id = (SELECT scid
                              FROM cte
                              WHERE cid = c_update.id
                              LIMIT 1)
WHERE c_update.id IN (SELECT cid FROM cte);

Run at your own risk.

What it does?

It writes the sales channel and the customer_id of all customer with more than one non-guest account and orders with the same email to a “temporary table” which is no temporary table but a common table expression (cte), but only if there is only one sales channel involved.

Then this cte is used to update the customer table, circumventing the “You can't specify target table for update in FROM clause“- problem.

If you are wondering, why a LIMIT 1 is in there? Because more than one row is coming back and first I thought it is a mistake or superfluous/theoretical error, but of course not, MySQL knows better: There are customer with more than one order in this one sales channel and therefore it might happen, that the same data is multiple times in the CTE and therefore LIMITing to 1 doesn’t make problems!