Move Orders to Archive

April 10, 2025 · AI Conversations

User
Put order before 2021 from posts and postsmeta tables to respective archive tables like post_archive
Postmeta_archive
Assistant
To move records from the `posts` and `postmeta` tables into archive tables (`post_archive` and `postmeta_archive`) where the post was created **before 2021**, you can run the following SQL statements:

### Step 1: Move posts to `post_archive`

INSERT INTO post_archive
SELECT * FROM posts
WHERE post_date < '2021-01-01';

### Step 2: Move postmeta to `postmeta_archive`

INSERT INTO postmeta_archive
SELECT pm.*
FROM postmeta pm
JOIN posts p ON pm.post_id = p.ID
WHERE p.post_date < '2021-01-01';

### Step 3: Delete moved posts from `posts`

DELETE FROM posts
WHERE post_date < '2021-01-01';

### Step 4: Delete moved postmeta from `postmeta`

DELETE pm
FROM postmeta pm
JOIN posts p ON pm.post_id = p.ID
WHERE p.post_date < '2021-01-01';

> **Note**:
> – Ensure `post_archive` and `postmeta_archive` tables have the same structure as their source tables.
> – It’s **highly recommended to take a full database backup** before running these queries.

Would you like help creating the archive tables or checking if they already exist?

Assistant
To move orders (custom post type `shop_order`) **before 2021** from the `wp_posts` and `wp_postmeta` tables to archive tables (`post_archive`, `postmeta_archive`), you can run the following SQL queries:

> **Note:** Make sure you create the `post_archive` and `postmeta_archive` tables with the same structure as `wp_posts` and `wp_postmeta` respectively and **make a full database backup** before running any queries.

—

### Step 1: Create Archive Tables (if not already created)

CREATE TABLE IF NOT EXISTS post_archive LIKE wp_posts;
CREATE TABLE IF NOT EXISTS postmeta_archive LIKE wp_postmeta;

—

### Step 2: Insert Orders (Before 2021) Into Archive Tables

-- Insert into post_archive
INSERT INTO post_archive
SELECT * FROM wp_posts
WHERE post_type = 'shop_order'
  AND post_date < '2021-01-01';

-- Insert into postmeta_archive
INSERT INTO postmeta_archive
SELECT pm.*
FROM wp_postmeta pm
JOIN wp_posts p ON p.ID = pm.post_id
WHERE p.post_type = 'shop_order'
  AND p.post_date < '2021-01-01';

—

### Step 3: Delete Orders from Original Tables

-- Delete from wp_postmeta
DELETE pm FROM wp_postmeta pm
JOIN wp_posts p ON p.ID = pm.post_id
WHERE p.post_type = 'shop_order'
  AND p.post_date < '2021-01-01';

-- Delete from wp_posts
DELETE FROM wp_posts
WHERE post_type = 'shop_order'
  AND post_date < '2021-01-01';

—

Let me know if you want to filter by a specific status (like `completed` orders only) or need a rollback script too.

User
For woocommerce order related
Assistant
Thanks for the clarification! Here's the **WooCommerce-specific version** to archive **orders before 2021** from the `wp_posts` and `wp_postmeta` tables to `post_archive` and `postmeta_archive`.

WooCommerce orders are stored as:
– Post type: `shop_order`
– In `wp_posts` and `wp_postmeta` tables.

—

### Step 1: Create Archive Tables (if not already created)

CREATE TABLE IF NOT EXISTS post_archive LIKE wp_posts;
CREATE TABLE IF NOT EXISTS postmeta_archive LIKE wp_postmeta;

—

### Step 2: Move WooCommerce Orders Before 2021

#### Insert Orders Into `post_archive`

INSERT INTO post_archive
SELECT * FROM wp_posts
WHERE post_type = 'shop_order'
  AND post_date < '2021-01-01';

#### Insert Order Meta Into `postmeta_archive`

INSERT INTO postmeta_archive
SELECT pm.*
FROM wp_postmeta pm
INNER JOIN wp_posts p ON p.ID = pm.post_id
WHERE p.post_type = 'shop_order'
  AND p.post_date < '2021-01-01';

—

### Step 3: Delete Archived Orders from Original Tables

#### Delete from `wp_postmeta`

DELETE pm FROM wp_postmeta pm
INNER JOIN wp_posts p ON p.ID = pm.post_id
WHERE p.post_type = 'shop_order'
  AND p.post_date < '2021-01-01';

#### Delete from `wp_posts`

DELETE FROM wp_posts
WHERE post_type = 'shop_order'
  AND post_date < '2021-01-01';

—

### Optional: Also Archive WooCommerce Order Notes

If you also want to move **order notes** (`post_type = 'shop_order_note'`) linked to those orders, let me know — I can help you handle those too.

Would you like a rollback/restore query too, just in case?

User
Yes
Assistant
Great! Here’s how you can **rollback/restore the archived WooCommerce orders** from `post_archive` and `postmeta_archive` back to the main tables (`wp_posts` and `wp_postmeta`).

—

### **Restore Step 1: Move Orders Back to `wp_posts`**

INSERT INTO wp_posts
SELECT * FROM post_archive
WHERE post_type = 'shop_order'
  AND post_date < '2021-01-01';

—

### **Restore Step 2: Move Order Meta Back to `wp_postmeta`**

INSERT INTO wp_postmeta
SELECT pm.*
FROM postmeta_archive pm
JOIN post_archive pa ON pm.post_id = pa.ID
WHERE pa.post_type = 'shop_order'
  AND pa.post_date < '2021-01-01';

—

### **Restore Step 3: Clean the Archive Tables (Optional)**
Only run this if you're confident everything is restored:

DELETE FROM postmeta_archive
WHERE post_id IN (
  SELECT ID FROM post_archive
  WHERE post_type = 'shop_order'
    AND post_date < '2021-01-01'
);

DELETE FROM post_archive
WHERE post_type = 'shop_order'
  AND post_date < '2021-01-01';

—

Let me know if you want to wrap these into a stored procedure or CLI command for WP-CLI or automate monthly archiving.

User
But post meta tables is 9 gb may archiving orders takes time or timeout
Assistant
You're absolutely right — if your `wp_postmeta` table is 9 GB, **archiving thousands of orders at once** can cause **timeouts, locking issues, or high memory usage**.

### Here's how you can **safely archive in chunks**:

—

### **1. Use a Batch/Chunk Approach**
Process, say, **500 or 1000 orders at a time** to stay within resource limits.

#### Step A: Create Archive Tables (Only once)

CREATE TABLE IF NOT EXISTS post_archive LIKE wp_posts;
CREATE TABLE IF NOT EXISTS postmeta_archive LIKE wp_postmeta;

—

#### Step B: Move 500 Orders Per Batch (Adjust as needed)

-- Step 1: Get a batch of order IDs before 2021
SELECT ID INTO TEMPORARY TABLE temp_order_ids
FROM wp_posts
WHERE post_type = 'shop_order'
  AND post_date < '2021-01-01'
ORDER BY ID
LIMIT 500;

-- Step 2: Archive posts
INSERT INTO post_archive
SELECT * FROM wp_posts
WHERE ID IN (SELECT ID FROM temp_order_ids);

-- Step 3: Archive related postmeta
INSERT INTO postmeta_archive
SELECT * FROM wp_postmeta
WHERE post_id IN (SELECT ID FROM temp_order_ids);

-- Step 4: Delete postmeta
DELETE FROM wp_postmeta
WHERE post_id IN (SELECT ID FROM temp_order_ids);

-- Step 5: Delete posts
DELETE FROM wp_posts
WHERE ID IN (SELECT ID FROM temp_order_ids);

Repeat the above in a loop or run it multiple times manually/scheduled.

—

### **2. Automate with a PHP Script or WP-CLI**
You can create a **WP-CLI command or PHP cron job** that runs this logic periodically (e.g., via `wp cron event schedule`) to avoid heavy server load.

Would you like help writing a PHP/WP-CLI version that you can run on the server instead of SQL?

User
Yes
Assistant
Perfect! Here's a **WP-CLI command** you can run safely on your server to archive WooCommerce orders **in chunks** without overloading memory or running into timeouts.

—

## **1. Add a Custom WP-CLI Command**

Create a file, e.g. `archive-orders.php` inside your `wp-content/mu-plugins` or a custom plugin.

<?php
if (defined('WP_CLI') && WP_CLI) {
    WP_CLI::add_command('woo archive_orders', 'archive_woocommerce_orders');
}

function archive_woocommerce_orders($args, $assoc_args) {
    global $wpdb;

    $limit = isset($assoc_args['limit']) ? intval($assoc_args['limit']) : 500;

    // Step 1: Get order IDs before 2021
    $order_ids = $wpdb->get_col($wpdb->prepare("
        SELECT ID FROM {$wpdb->posts}
        WHERE post_type = 'shop_order'
        AND post_date < %s
        ORDER BY ID ASC
        LIMIT %d
    ", '2021-01-01', $limit));

    if (empty($order_ids)) {
        WP_CLI::success("No more orders to archive.");
        return;
    }

    $id_list = implode(',', array_map('intval', $order_ids));

    // Step 2: Insert orders into post_archive
    $wpdb->query("
        INSERT INTO post_archive
        SELECT * FROM {$wpdb->posts}
        WHERE ID IN ($id_list)
    ");

    // Step 3: Insert order meta into postmeta_archive
    $wpdb->query("
        INSERT INTO postmeta_archive
        SELECT * FROM {$wpdb->postmeta}
        WHERE post_id IN ($id_list)
    ");

    // Step 4: Delete from wp_postmeta
    $wpdb->query("
        DELETE FROM {$wpdb->postmeta}
        WHERE post_id IN ($id_list)
    ");

    // Step 5: Delete from wp_posts
    $wpdb->query("
        DELETE FROM {$wpdb->posts}
        WHERE ID IN ($id_list)
    ");

    WP_CLI::success("Archived " . count($order_ids) . " orders.");
}

—

## **2. Run the Command via CLI**
After saving the file:

wp woo archive_orders --limit=500

You can repeat this command until all orders before 2021 are archived.

—

## **3. Optional Automation**
Add to a cron job:

*/10 * * * * /path/to/wp-cli.phar --path=/var/www/html wp woo archive_orders --limit=500 >> /var/log/wp-archive.log 2>&1

—

Would you like a restore WP-CLI command too, in case you need to unarchive?

User
count wooocommerce order before and equal to 1 jan 2022
Assistant
To **count the total number of WooCommerce orders before or equal to January 1, 2022**, you can run the following SQL query directly:

### ✅ **SQL Query**

SELECT COUNT(*) AS total_orders
FROM wp_posts
WHERE post_type = 'shop_order'
  AND post_date <= '2022-01-01';

—

### ✅ **WP-CLI Equivalent**
If you prefer using WP-CLI:

wp db query "SELECT COUNT(*) AS total_orders FROM wp_posts WHERE post_type = 'shop_order' AND post_date <= '2022-01-01';"

—

Let me know if you want to filter by specific order statuses (like `wc-completed`) or include archived orders too.

Leave a Comment

Your email address will not be published. Required fields are marked *