Cleaning Up Guest Users Without Orders in Shopware 5

Cleaning Up Guest Users Without Orders in Shopware 5

Over time, your Shopware 5 store may accumulate a large number of guest users who have never placed an order. These unused accounts take up database space and may clutter your customer management system. To keep your Shopware database clean and efficient, it is a good practice to periodically remove guest users who have no associated orders.

Why Remove Guest Users Without Orders?

Guest accounts are automatically created when users browse and interact with your store but do not complete a purchase. If left unchecked, thousands of such accounts may accumulate over time, leading to:

• Unnecessary database clutter

• Slower query performance

• Inefficient customer management

By removing these inactive guest accounts, you can improve your store's performance and keep your customer database well-organized.

How to Remove Guest Users Without Orders

To safely delete guest users who have never placed an order, you can use a PHP script that:

1. Identifies all guest users in the database.

2. Checks if they have any associated orders.

3. Deletes the guest users who have no orders.

Running the Cleanup Script via SSH

Since this process involves database operations, you will need to run the script on your Shopware server via SSH. Follow these steps:

1. Connect to Your Server

Log in to your Shopware server via SSH:

ssh your-user@your-server-ip

2. Navigate to Your Shopware Directory

Move to your Shopware installation directory:

cd /path/to/shopware

3. Execute the Cleanup Script

Run the script to remove unused guest accounts:

php remove_guest_users.php

Here is the php code for remove_guest_users.php:

boot();

// Get database connection
$db = $kernel->getContainer()->get('db');

// Logging function
function logMessage($message) {
    echo $message . PHP_EOL;
    file_put_contents(__DIR__ . '/var/log/delete_guest_users_log.txt', date('Y-m-d H:i:s') . ': ' . $message . PHP_EOL, FILE_APPEND);
}

logMessage('Starting process to delete guest users without orders...');

// Retrieve all guest users who have never placed an order
$sql = "SELECT u.id, u.email
        FROM s_user u
        LEFT JOIN s_order o ON u.id = o.userID
        WHERE u.accountmode = 1
        AND o.id IS NULL";

$guestUsers = $db->fetchAll($sql);

$totalGuests = count($guestUsers);
logMessage("Found {$totalGuests} guest users without orders. Proceeding with deletion.");

if ($totalGuests == 0) {
    logMessage("No eligible guest users to delete. Script exiting.");
    exit;
}

$db->beginTransaction();
try {
    $guestIds = array_column($guestUsers, 'id');
    $placeholders = implode(',', array_fill(0, count($guestIds), '?'));

    // Delete these guest users
    $db->executeUpdate("DELETE FROM s_user WHERE id IN ($placeholders)", $guestIds);

    $db->commit();
    logMessage("Successfully deleted {$totalGuests} guest users without orders.");

    // Log each deleted user's email
    foreach ($guestUsers as $user) {
        logMessage("Deleted guest user: ID={$user['id']}, Email={$user['email']}");
    }
} catch (Exception $e) {
    $db->rollBack();
    logMessage("Error occurred while deleting guest users without orders: " . $e->getMessage());
    exit(1);
}

logMessage("All eligible guest users without orders have been processed.");

Best Practices

• Backup Your Database: Always create a backup before running any cleanup script to avoid accidental data loss.

• Schedule Regular Cleanups: Set up a scheduled task (cron job) to run this script periodically.

• Monitor Database Performance: Regularly check your database performance to ensure that removing guest users has a positive impact.

Conclusion

Cleaning up guest users without orders is a simple yet effective way to maintain a well-organized and efficient Shopware 5 store. By periodically running a cleanup script, you can optimize your database, improve system performance, and streamline customer management.