Comments and reviews migration during website migration

Implementing Comments and Reviews Migration During Site Migration

Comments and reviews are user content with history and relationships. During transfer, important to preserve reply tree, authorship, dates and moderation statuses.

Comment Data Structure

Standard structure (WordPress example):

-- wp_comments
comment_ID, comment_post_ID, comment_author, comment_author_email,
comment_author_url, comment_date, comment_content, comment_approved,
comment_parent (for replies), user_id (if registered)

Differences for reviews (WooCommerce):

-- Review = comment with type='review'
-- Rating stored in wp_commentmeta: key='rating', value=1-5

Export from WordPress

def export_comments(wp_db):
    cursor = wp_db.cursor(dictionary=True)
    cursor.execute("""
        SELECT
            c.comment_ID as id,
            c.comment_post_ID as post_legacy_id,
            c.comment_author as author_name,
            c.comment_author_email as author_email,
            c.comment_date_gmt as created_at,
            c.comment_content as content,
            c.comment_approved as status,
            c.comment_parent as parent_id,
            c.user_id as wp_user_id,
            c.comment_type as type,
            cm.meta_value as rating
        FROM wp_comments c
        LEFT JOIN wp_commentmeta cm ON c.comment_ID = cm.comment_id
            AND cm.meta_key = 'rating'
        WHERE c.comment_type IN ('comment', 'review', '')
        ORDER BY c.comment_parent ASC, c.comment_ID ASC
    """)
    return cursor.fetchall()

Important: ORDER BY comment_parent ASC — parent comments processed before children.

Transfer Preserving Reply Tree

def migrate_comments(wp_db, new_db, post_id_map, user_id_map):
    comments = export_comments(wp_db)

    # Mapping old comment_ID → new
    comment_id_map = {}

    for comment in comments:
        # Find new post ID
        new_post_id = post_id_map.get(comment['post_legacy_id'])
        if not new_post_id:
            continue  # post not transferred — skip

        # Find new parent ID
        new_parent_id = None
        if comment['parent_id']:
            new_parent_id = comment_id_map.get(comment['parent_id'])
            if not new_parent_id:
                print(f"Parent comment {comment['parent_id']} not found, skipping child")
                continue

        # Determine status
        status_map = {'1': 'approved', '0': 'pending', 'spam': 'spam', 'trash': 'deleted'}
        status = status_map.get(str(comment['status']), 'pending')

        new_comment = {
            'post_id': new_post_id,
            'author_name': comment['author_name'],
            'author_email': comment['author_email'],
            'content': comment['content'],
            'status': status,
            'parent_id': new_parent_id,
            'user_id': user_id_map.get(comment['wp_user_id']),
            'created_at': comment['created_at'],
            'rating': int(comment['rating']) if comment['rating'] else None,
            'legacy_id': comment['id'],
        }

        new_id = new_db.create_comment(new_comment)
        comment_id_map[comment['id']] = new_id

    print(f"Migrated {len(comment_id_map)} comments")
    return comment_id_map

Reviews from Disqus

def export_disqus_comments(disqus_api_key, forum_shortname):
    """Export comments from Disqus via API"""
    import requests

    all_posts = []
    cursor = None

    while True:
        params = {
            'api_key': disqus_api_key,
            'forum': forum_shortname,
            'limit': 100,
        }
        if cursor:
            params['cursor'] = cursor

        resp = requests.get('https://disqus.com/api/3.0/posts/list.json', params=params)
        data = resp.json()

        all_posts.extend(data['response'])

        if not data['cursor']['hasNext']:
            break
        cursor = data['cursor']['next']

    return all_posts

Disqus also provides XML export via admin panel (Settings → Export). WXEP format compatible with WordPress XML import.

Product Reviews/Ratings Transfer (e-commerce)

def migrate_product_reviews(wp_db, new_db, product_id_map, user_id_map):
    cursor = wp_db.cursor(dictionary=True)
    cursor.execute("""
        SELECT
            c.comment_ID, c.comment_post_ID, c.comment_author,
            c.comment_author_email, c.comment_content, c.comment_date_gmt,
            c.comment_approved,
            MAX(CASE WHEN cm.meta_key = 'rating' THEN cm.meta_value END) as rating,
            MAX(CASE WHEN cm.meta_key = 'verified' THEN cm.meta_value END) as verified
        FROM wp_comments c
        JOIN wp_commentmeta cm ON c.comment_ID = cm.comment_id
        WHERE c.comment_type = 'review'
        GROUP BY c.comment_ID
    """)

    for review in cursor.fetchall():
        new_product_id = product_id_map.get(review['comment_post_ID'])
        if not new_product_id:
            continue

        new_db.create_review({
            'product_id': new_product_id,
            'author_name': review['comment_author'],
            'author_email': review['comment_author_email'],
            'content': review['comment_content'],
            'rating': int(review['rating'] or 5),
            'is_verified': review['verified'] == '1',
            'status': 'approved' if review['comment_approved'] == '1' else 'pending',
            'created_at': review['comment_date_gmt'],
        })

Verification After Transfer

def verify_comments_migration(wp_db, new_db, post_id_map):
    cursor = wp_db.cursor()
    cursor.execute("""
        SELECT comment_post_ID, COUNT(*) as count
        FROM wp_comments
        WHERE comment_approved = '1'
        GROUP BY comment_post_ID
    """)
    wp_counts = dict(cursor.fetchall())

    mismatches = []
    for wp_post_id, expected_count in wp_counts.items():
        new_post_id = post_id_map.get(wp_post_id)
        if not new_post_id:
            continue
        actual_count = new_db.count_comments(new_post_id)
        if actual_count != expected_count:
            mismatches.append((wp_post_id, expected_count, actual_count))

    if mismatches:
        print("Comment count mismatches:")
        for wp_id, expected, actual in mismatches:
            print(f"  Post {wp_id}: expected {expected}, got {actual}")
    return len(mismatches) == 0

Execution Time

Transfer comments preserving reply tree, authorship and ratings — 2–3 working days.