Làm việc với custom table trong WordPress: Hướng dẫn nâng cao

1. Kiến trúc tổng thể: Từ procedural đến OOP

Khi custom table chỉ có vài trăm bản ghi, viết hàm rời rạc vẫn ổn. Nhưng khi scale, bạn cần một kiến trúc rõ ràng:

• Model: Đại diện một row (data object).
• Repository: Tập trung mọi thao tác DB (query, insert, update, delete).
• Service: Business logic (ví dụ: “ghi log đọc manga + cập nhật trending cache”).
• Controller: Nhận request, gọi Service, trả response (REST API / Admin).

<?php
// Model: Đại diện một log entry
class Manga_Log_Model {
    public int $id = 0;
    public int $user_id = 0;
    public int $manga_id = 0;
    public int $chapter_id = 0;
    public string $action = 'read';
    public string $ip_address = '';
    public string $user_agent = '';
    public string $created_at = '';

    public static function from_db_object( object $row ): self {
        $instance = new self();
        foreach ( $row as $key => $value ) {
            if ( property_exists( $instance, $key ) ) {
                $instance->$key = $value;
            }
        }
        return $instance;
    }

    public function to_array(): array {
        return get_object_vars( $this );
    }
}

2. Schema Design & Indexing Strategy

Custom table chậm không phải vì WordPress, mà vì index sai. Một số nguyên tắc vàng:

• Primary Key: Luôn là bigint unsigned AUTO_INCREMENT.
• Foreign Key: WordPress không enforce FK ở DB level, nhưng nên đặt tên cột rõ ràng (user_id, manga_id) và xử lý integrity ở app level.
• Composite Index: Đặt theo thứ tự EQUAL -> RANGE -> ORDER BY. Ví dụ query WHERE manga_id = 123 AND action = 'read' ORDER BY created_at DESC thì index tối ưu là KEY idx_manga_action_created (manga_id, action, created_at).
• Covering Index: Nếu query chỉ cần user_id và created_at, hãy để cả 2 cột trong index để MySQL không phải đọc row data.
• Partitioning: Với bảng > 10 triệu rows (time-series như log, analytics), cân nhắc RANGE PARTITION theo created_at hoặc tách bảng archive.

<?php
function init_create_advanced_table() {
    global $wpdb;
    $table = $wpdb->prefix . 'manga_logs';
    $charset = $wpdb->get_charset_collate();

    $sql = "CREATE TABLE $table (
        id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        user_id bigint(20) unsigned NOT NULL DEFAULT 0,
        manga_id bigint(20) unsigned NOT NULL DEFAULT 0,
        chapter_id bigint(20) unsigned NOT NULL DEFAULT 0,
        action varchar(50) NOT NULL DEFAULT '',
        ip_address varchar(100) NOT NULL DEFAULT '',
        user_agent varchar(255) NOT NULL DEFAULT '',
        created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        KEY idx_user_created (user_id, created_at),
        KEY idx_manga_action_created (manga_id, action, created_at),
        KEY idx_created (created_at)
    ) $charset;";

    require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
    dbDelta( $sql );
}

Tip: Dùng EXPLAIN trước mọi query phức tạp. Nếu Extra hiện Using filesort hoặc Using temporary, index của bạn chưa tối ưu.

3. Repository Pattern + Dependency Injection

Thay vì global $wpdb rải rác, inject dependency và dùng interface để dễ mock khi test:

<?php
interface Manga_Log_Repository_Interface {
    public function insert( Manga_Log_Model $log ): int|false;
    public function get_by_manga( int $manga_id, array $args = [] ): array;
    public function get_trending( int $days = 7, int $limit = 10 ): array;
    public function delete_old( int $days ): int;
}

class Manga_Log_Repository implements Manga_Log_Repository_Interface {
    private wpdb $db;
    private string $table;

    public function __construct( wpdb $db ) {
        $this->db = $db;
        $this->table = $db->prefix . 'manga_logs';
    }

    public function insert( Manga_Log_Model $log ): int|false {
        $data = [
            'user_id'    => $log->user_id,
            'manga_id'   => $log->manga_id,
            'chapter_id' => $log->chapter_id,
            'action'     => $log->action,
            'ip_address' => $log->ip_address,
            'user_agent' => $log->user_agent,
            'created_at' => $log->created_at ?: current_time( 'mysql' ),
        ];

        $result = $this->db->insert( $this->table, $data, 
            [ '%d', '%d', '%d', '%s', '%s', '%s', '%s' ] );

        return $result ? $this->db->insert_id : false;
    }

    public function get_by_manga( int $manga_id, array $args = [] ): array {
        $defaults = [
            'action'   => '',
            'per_page' => 20,
            'page'     => 1,
            'orderby'  => 'created_at',
            'order'    => 'DESC',
        ];
        $args = wp_parse_args( $args, $defaults );
        $offset = ( $args['page'] - 1 ) * $args['per_page'];

        $where = [ $this->db->prepare( 'manga_id = %d', $manga_id ) ];
        if ( ! empty( $args['action'] ) ) {
            $where[] = $this->db->prepare( 'action = %s', $args['action'] );
        }

        $where_sql = implode( ' AND ', $where );
        $orderby = sanitize_sql_orderby( $args['orderby'] . ' ' . $args['order'] );

        $sql = "SELECT * FROM {$this->table} WHERE {$where_sql} ORDER BY {$orderby} LIMIT %d OFFSET %d";
        $prepared = $this->db->prepare( $sql, $args['per_page'], $offset );

        $rows = $this->db->get_results( $prepared );
        return array_map( [ Manga_Log_Model::class, 'from_db_object' ], $rows );
    }

    public function get_trending( int $days = 7, int $limit = 10 ): array {
        $sql = "SELECT manga_id, COUNT(*) as read_count
                FROM {$this->table}
                WHERE action = 'read'
                AND created_at >= DATE_SUB(NOW(), INTERVAL %d DAY)
                GROUP BY manga_id
                ORDER BY read_count DESC
                LIMIT %d";
        return $this->db->get_results( $this->db->prepare( $sql, $days, $limit ) );
    }

    public function delete_old( int $days ): int {
        $cutoff = date( 'Y-m-d H:i:s', strtotime( "-{$days} days" ) );
        return $this->db->query( $this->db->prepare(
            "DELETE FROM {$this->table} WHERE created_at < %s", $cutoff
        ) );
    }
}

// Service layer
class Manga_Log_Service {
    private Manga_Log_Repository_Interface $repo;

    public function __construct( Manga_Log_Repository_Interface $repo ) {
        $this->repo = $repo;
    }

    public function track_read( int $user_id, int $manga_id, int $chapter_id ): int|false {
        $log = new Manga_Log_Model();
        $log->user_id = $user_id;
        $log->manga_id = $manga_id;
        $log->chapter_id = $chapter_id;
        $log->action = 'read';
        $log->ip_address = $this->get_client_ip();
        $log->user_agent = sanitize_text_field( $_SERVER['HTTP_USER_AGENT'] ?? '' );

        $id = $this->repo->insert( $log );

        if ( $id ) {
            wp_cache_delete( "manga_trending_7", 'manga_stats' );
        }

        return $id;
    }

    private function get_client_ip(): string {
        $headers = [ 'HTTP_CF_CONNECTING_IP', 'HTTP_X_FORWARDED_FOR', 'REMOTE_ADDR' ];
        foreach ( $headers as $h ) {
            if ( ! empty( $_SERVER[ $h ] ) ) {
                $ips = explode( ',', $_SERVER[ $h ] );
                return sanitize_text_field( trim( $ips[0] ) );
            }
        }
        return '0.0.0.0';
    }
}

4. Transaction & Concurrency Control

WordPress không tự động transaction. Khi một thao tác gồm nhiều query, bạn phải tự wrap:

<?php
class Manga_Log_Repository {
    public function insert_with_stats( Manga_Log_Model $log ): int|false {
        $this->db->query( 'START TRANSACTION' );

        try {
            $id = $this->insert( $log );
            if ( ! $id ) {
                throw new Exception( 'Insert failed: ' . $this->db->last_error );
            }

            // Cập nhật bảng summary
            $this->db->query( $this->db->prepare(
                "INSERT INTO {$this->db->prefix}manga_stats (manga_id, read_count, last_read)
                 VALUES (%d, 1, NOW())
                 ON DUPLICATE KEY UPDATE read_count = read_count + 1, last_read = NOW()",
                $log->manga_id
            ) );

            $this->db->query( 'COMMIT' );
            return $id;

        } catch ( Exception $e ) {
            $this->db->query( 'ROLLBACK' );
            error_log( 'Transaction failed: ' . $e->getMessage() );
            return false;
        }
    }
}

Lưu ý: MySQL InnoDB (mặc định của WordPress) hỗ trợ row-level locking. Nếu 2 request cùng update một row trong transaction, một sẽ đợi (lock wait). Tránh transaction dài hoặc chứa HTTP request bên ngoài.

5. Batch Processing & Bulk Operations

Insert 10,000 bản ghi bằng vòng lặp $wpdb->insert() sẽ tạo 10,000 round-trip đến DB. Thay vào đó, dùng bulk insert:

<?php
public function insert_batch( array $logs ): int {
    if ( empty( $logs ) ) return 0;

    $fields = [ 'user_id', 'manga_id', 'chapter_id', 'action', 'ip_address', 'user_agent', 'created_at' ];
    $placeholders = '(' . implode( ', ', array_fill( 0, count( $fields ), '%s' ) ) . ')';

    $values = [];
    foreach ( $logs as $log ) {
        $values[] = $log->user_id;
        $values[] = $log->manga_id;
        $values[] = $log->chapter_id;
        $values[] = $log->action;
        $values[] = $log->ip_address;
        $values[] = $log->user_agent;
        $values[] = $log->created_at ?: current_time( 'mysql' );
    }

    $chunks = array_chunk( $logs, 1000 ); // MySQL giới hạn max_packet_size
    $total = 0;

    foreach ( $chunks as $chunk ) {
        $chunk_values = [];
        foreach ( $chunk as $log ) {
            $chunk_values = array_merge( $chunk_values, array_values( $log->to_array() ) );
        }

        $sql = "INSERT INTO {$this->table} (" . implode( ', ', $fields ) . ") VALUES ";
        $sql .= implode( ', ', array_fill( 0, count( $chunk ), $placeholders ) );

        $result = $this->db->query( $this->db->prepare( $sql, ...$chunk_values ) );
        if ( $result !== false ) {
            $total += count( $chunk );
        }
    }

    return $total;
}

Chiến lược: Chia batch 500-1000 rows. Nếu server có max_allowed_packet = 16MB, đừng vượt quá giới hạn đó.

6. Caching Strategy & Cache Invalidation

Cache không khó, cache invalidation mới khó. Chiến lược cho custom table:

• Object Cache: Cache query phổ biến (trending, top 10) với TTL 5-15 phút.
• Transient: Dùng cho data ít thay đổi (tổng số log tháng này) với TTL dài (1-24 giờ).
• Cache Key Naming: Luôn include version và params. Ví dụ: manga_logs:v2:manga:123:action:read:page:1.
• Invalidation: Xóa cache ngay khi insert/delete, không đợi TTL hết.

<?php
class Manga_Log_Cache {
    private string $group = 'manga_logs';

    public function get_trending( int $days = 7 ): array {
        $key = "trending:{$days}";
        $cached = wp_cache_get( $key, $this->group );
        if ( false !== $cached ) return $cached;

        // Nếu không có object cache, thử transient
        $transient = get_transient( "manga_trending_{$days}" );
        if ( false !== $transient ) {
            wp_cache_set( $key, $transient, $this->group, 300 );
            return $transient;
        }

        return [];
    }

    public function set_trending( array $data, int $days = 7 ): void {
        $key = "trending:{$days}";
        wp_cache_set( $key, $data, $this->group, 300 );
        set_transient( "manga_trending_{$days}", $data, HOUR_IN_SECONDS );
    }

    public function invalidate_manga( int $manga_id ): void {
        wp_cache_delete( "trending:7", $this->group );
        wp_cache_delete( "trending:30", $this->group );
        delete_transient( 'manga_trending_7' );
        delete_transient( 'manga_trending_30' );

        // Xóa cache pagination liên quan
        for ( $i = 1; $i <= 5; $i++ ) {
            wp_cache_delete( "manga:{$manga_id}:page:{$i}", $this->group );
        }
    }
}

<?php
add_action( 'rest_api_init', function () {
    register_rest_route( 'manga/v1', '/logs', [
        'methods'             => 'GET',
        'callback'            => 'manga_logs_rest_get_items',
        'permission_callback' => function () {
            return current_user_can( 'read' );
        },
        'args' => [
            'manga_id'  => [ 'default' => 0, 'sanitize_callback' => 'absint' ],
            'action'    => [ 'default' => '', 'sanitize_callback' => 'sanitize_key' ],
            'per_page'  => [ 'default' => 20, 'sanitize_callback' => 'absint' ],
            'page'      => [ 'default' => 1, 'sanitize_callback' => 'absint' ],
            'orderby'   => [ 'default' => 'created_at', 'enum' => [ 'created_at', 'id' ], 'sanitize_callback' => 'sanitize_key' ],
            'order'     => [ 'default' => 'DESC', 'enum' => [ 'ASC', 'DESC' ], 'sanitize_callback' => 'strtoupper' ],
        ],
    ] );
} );

function manga_logs_rest_get_items( WP_REST_Request $request ): WP_REST_Response {
    $repo = new Manga_Log_Repository( $GLOBALS['wpdb'] );

    $args = [
        'action'   => $request->get_param( 'action' ),
        'per_page' => min( $request->get_param( 'per_page' ), 100 ), // Hard limit
        'page'     => $request->get_param( 'page' ),
        'orderby'  => $request->get_param( 'orderby' ),
        'order'    => $request->get_param( 'order' ),
    ];

    $manga_id = $request->get_param( 'manga_id' );
    $items = $repo->get_by_manga( $manga_id, $args );

    // Tính total cho pagination header
    $total = $GLOBALS['wpdb']->get_var( "SELECT COUNT(*) FROM {$GLOBALS['wpdb']->prefix}manga_logs WHERE manga_id = " . absint( $manga_id ) );

    $response = new WP_REST_Response( $items, 200 );
    $response->header( 'X-WP-Total', $total );
    $response->header( 'X-WP-TotalPages', ceil( $total / $args['per_page'] ) );

    return $response;
}

<?php
if ( defined( 'WP_CLI' ) && WP_CLI ) {
    class Manga_Logs_Advanced_CLI {

        public function stats( $args, $assoc_args ) {
            global $wpdb;
            $table = $wpdb->prefix . 'manga_logs';

            $total = $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" );
            $today = $wpdb->get_var( "SELECT COUNT(*) FROM {$table} WHERE DATE(created_at) = CURDATE()" );

            WP_CLI::line( "Total logs: {$total}" );
            WP_CLI::line( "Today logs: {$today}" );
        }

        public function export( $args, $assoc_args ) {
            $output = $assoc_args['output'] ?? 'php://stdout';
            $format = $assoc_args['format'] ?? 'csv';

            global $wpdb;
            $rows = $wpdb->get_results( "SELECT * FROM {$wpdb->prefix}manga_logs", ARRAY_A );

            if ( $format === 'csv' ) {
                $fp = fopen( $output, 'w' );
                if ( $rows ) {
                    fputcsv( $fp, array_keys( $rows[0] ) );
                    foreach ( $rows as $row ) fputcsv( $fp, $row );
                }
                fclose( $fp );
            } elseif ( $format === 'json' ) {
                file_put_contents( $output, json_encode( $rows, JSON_PRETTY_PRINT ) );
            }

            WP_CLI::success( "Exported {$format} to {$output}" );
        }

        public function cleanup( $args, $assoc_args ) {
            $days = absint( $assoc_args['days'] ?? 90 );
            $dry_run = isset( $assoc_args['dry-run'] );

            global $wpdb;
            $table = $wpdb->prefix . 'manga_logs';
            $cutoff = date( 'Y-m-d H:i:s', strtotime( "-{$days} days" ) );

            $count = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE created_at < %s", $cutoff ) );

            WP_CLI::line( "Found {$count} records older than {$days} days." );

            if ( $dry_run ) {
                WP_CLI::warning( 'Dry run mode. No records deleted.' );
                return;
            }

            $deleted = $wpdb->query( $wpdb->prepare( "DELETE FROM {$table} WHERE created_at < %s", $cutoff ) );
            WP_CLI::success( "Deleted {$deleted} records." );
        }
    }

    WP_CLI::add_command( 'manga-logs', 'Manga_Logs_Advanced_CLI' );
}

<?php
// Khi user đọc chương
add_action( 'manga_chapter_read', 'manga_schedule_log', 10, 3 );

function manga_schedule_log( int $user_id, int $manga_id, int $chapter_id ) {
    if ( ! function_exists( 'as_enqueue_async_action' ) ) return;

    as_enqueue_async_action( 'manga_process_log', [
        'user_id'    => $user_id,
        'manga_id'   => $manga_id,
        'chapter_id' => $chapter_id,
        'ip'         => $_SERVER['REMOTE_ADDR'] ?? '',
        'ua'         => $_SERVER['HTTP_USER_AGENT'] ?? '',
    ], 'manga_logs' );
}

add_action( 'manga_process_log', 'manga_process_log_callback', 10, 1 );

function manga_process_log_callback( array $data ) {
    $repo = new Manga_Log_Repository( $GLOBALS['wpdb'] );
    $service = new Manga_Log_Service( $repo );
    $service->track_read( $data['user_id'], $data['manga_id'], $data['chapter_id'] );
}

<?php
class Manga_Log_Migration {
    private string $version_option = 'manga_log_schema_version';
    private string $target_version = '1.3.0';

    public function run() {
        $current = get_option( $this->version_option, '1.0.0' );
        if ( version_compare( $current, $this->target_version, '>=' ) ) return;

        $migrations = [
            '1.1.0' => 'migrate_1_1_0',
            '1.2.0' => 'migrate_1_2_0',
            '1.3.0' => 'migrate_1_3_0',
        ];

        foreach ( $migrations as $version => $method ) {
            if ( version_compare( $current, $version, '<' ) ) {
                $this->backup_table();
                $this->$method();
                update_option( $this->version_option, $version );
                $current = $version;
            }
        }
    }

    private function backup_table() {
        global $wpdb;
        $table = $wpdb->prefix . 'manga_logs';
        $backup = $wpdb->prefix . 'manga_logs_backup_' . date( 'Ymd_His' );
        $wpdb->query( "CREATE TABLE {$backup} LIKE {$table}" );
        $wpdb->query( "INSERT INTO {$backup} SELECT * FROM {$table}" );
    }

    private function migrate_1_1_0() {
        global $wpdb;
        $table = $wpdb->prefix . 'manga_logs';
        $wpdb->query( "ALTER TABLE {$table} ADD COLUMN ip_address varchar(100) NOT NULL DEFAULT '' AFTER action" );
    }

    private function migrate_1_2_0() {
        global $wpdb;
        $table = $wpdb->prefix . 'manga_logs';
        $wpdb->query( "ALTER TABLE {$table} ADD COLUMN user_agent varchar(255) NOT NULL DEFAULT '' AFTER ip_address" );
        $wpdb->query( "ALTER TABLE {$table} ADD KEY idx_user_created (user_id, created_at)" );
    }

    private function migrate_1_3_0() {
        global $wpdb;
        $table = $wpdb->prefix . 'manga_logs';
        $wpdb->query( "ALTER TABLE {$table} ADD COLUMN chapter_id bigint(20) unsigned NOT NULL DEFAULT 0 AFTER manga_id" );
    }
}

// Hook vào admin_init hoặc plugins_loaded
add_action( 'plugins_loaded', [ new Manga_Log_Migration(), 'run' ] );

<?php
function manga_rate_limit_check( string $ip ): bool {
    $key = 'manga_rate_' . md5( $ip );
    $count = get_transient( $key );
    if ( false === $count ) {
        set_transient( $key, 1, MINUTE_IN_SECONDS );
        return true;
    }
    if ( $count >= 60 ) { // 60 request/phút
        return false;
    }
    set_transient( $key, $count + 1, MINUTE_IN_SECONDS );
    return true;
}

<?php
// Counter table để tránh COUNT(*) toàn bộ
class Manga_Log_Counter {
    public function increment( int $manga_id ): void {
        global $wpdb;
        $wpdb->query( $wpdb->prepare(
            "INSERT INTO {$wpdb->prefix}manga_counters (manga_id, read_count) VALUES (%d, 1)
             ON DUPLICATE KEY UPDATE read_count = read_count + 1",
            $manga_id
        ) );
    }

    public function get( int $manga_id ): int {
        global $wpdb;
        return (int) $wpdb->get_var( $wpdb->prepare(
            "SELECT read_count FROM {$wpdb->prefix}manga_counters WHERE manga_id = %d",
            $manga_id
        ) );
    }
}

<?php
// Dùng $wpdb->base_prefix thay vì $wpdb->prefix
$table = $wpdb->base_prefix . 'manga_logs_network'; // wp_manga_logs_network

// Hoặc kiểm tra is_multisite()
if ( is_multisite() ) {
    $table = $wpdb->base_prefix . 'manga_logs';
} else {
    $table = $wpdb->prefix . 'manga_logs';
}

<?php
class Test_Manga_Log_Repository extends WP_UnitTestCase {
    private Manga_Log_Repository $repo;

    public function set_up() {
        parent::set_up();
        global $wpdb;
        $this->repo = new Manga_Log_Repository( $wpdb );
    }

    public function test_insert_and_retrieve() {
        $user = $this->factory()->user->create();
        wp_set_current_user( $user );

        $log = new Manga_Log_Model();
        $log->user_id = $user;
        $log->manga_id = 999;
        $log->action = 'read';

        $id = $this->repo->insert( $log );
        $this->assertIsInt( $id );
        $this->assertGreaterThan( 0, $id );

        $items = $this->repo->get_by_manga( 999, [ 'per_page' => 10 ] );
        $this->assertCount( 1, $items );
        $this->assertEquals( 'read', $items[0]->action );
    }

    public function test_pagination() {
        // Insert 25 rows
        for ( $i = 1; $i <= 25; $i++ ) {
            $log = new Manga_Log_Model();
            $log->user_id = 1;
            $log->manga_id = 100;
            $log->action = 'read';
            $this->repo->insert( $log );
        }

        $page1 = $this->repo->get_by_manga( 100, [ 'per_page' => 10, 'page' => 1 ] );
        $page2 = $this->repo->get_by_manga( 100, [ 'per_page' => 10, 'page' => 2 ] );

        $this->assertCount( 10, $page1 );
        $this->assertCount( 10, $page2 );
    }

    public function test_trending() {
        $log = new Manga_Log_Model();
        $log->user_id = 1;
        $log->manga_id = 200;
        $log->action = 'read';
        $this->repo->insert( $log );
        $this->repo->insert( $log );
        $this->repo->insert( $log );

        $trending = $this->repo->get_trending( 7, 5 );
        $this->assertNotEmpty( $trending );
        $this->assertEquals( 200, $trending[0]->manga_id );
        $this->assertEquals( 3, $trending[0]->read_count );
    }
}