techtac

classes/RegistrationsTable.php

function prepare_items()
{
    global $wpdb;

    // Setup table columns
    $columns               = $this->get_columns();
    $hidden                = [];
    $sortable              = [];
    $this->_column_headers = [$columns, $hidden, $sortable];

    // Pagination settings
    $per_page     = 25;
    $current_page = max(1, (int) $this->get_pagenum());
    $offset       = ($current_page - 1) * $per_page;

    /**
     * Case 1: Filter by post + search
     * Original behaviour preserved (no optimization here)
     */
    if (isset($_GET['post'], $_GET['search']) && !empty($_GET['post']) && !empty($_GET['search'])) {
        check_admin_referer('search-mollie-forms-registrations');

        $registrations = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT r.* 
                 FROM {$this->mollieForms->getRegistrationsTable()} r 
                 LEFT JOIN {$this->mollieForms->getRegistrationFieldsTable()} rf 
                   ON rf.registration_id = r.id 
                 WHERE r.post_id = %d 
                   AND (r.description LIKE CONCAT('%%', %s, '%%') 
                        OR rf.value LIKE CONCAT('%%', %s, '%%')) 
                 GROUP BY r.id 
                 ORDER BY r.id DESC",
                esc_sql(sanitize_text_field($_GET['post'])),
                esc_sql(sanitize_text_field($_GET['search'])),
                esc_sql(sanitize_text_field($_GET['search']))
            ),
            ARRAY_A
        );

        $total_items = count($registrations);
        $this->items = array_slice($registrations, $offset, $per_page);

    /**
     * Case 2: Filter by post only
     * Original behaviour preserved
     */
    } elseif (isset($_GET['post']) && !empty($_GET['post'])) {
        check_admin_referer('search-mollie-forms-registrations');

        $registrations = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT r.* 
                 FROM {$this->mollieForms->getRegistrationsTable()} r 
                 WHERE r.post_id = %d 
                 GROUP BY r.id 
                 ORDER BY r.id DESC",
                esc_sql(sanitize_text_field($_GET['post']))
            ),
            ARRAY_A
        );

        $total_items = count($registrations);
        $this->items = array_slice($registrations, $offset, $per_page);

    /**
     * Case 3: Search only
     * Original behaviour preserved
     */
    } elseif (isset($_GET['search']) && !empty($_GET['search'])) {
        check_admin_referer('search-mollie-forms-registrations');

        $registrations = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT r.* 
                 FROM {$this->mollieForms->getRegistrationsTable()} r 
                 LEFT JOIN {$this->mollieForms->getRegistrationFieldsTable()} rf 
                   ON rf.registration_id = r.id 
                 WHERE (r.description LIKE CONCAT('%%', %s, '%%') 
                        OR rf.value LIKE CONCAT('%%', %s, '%%')) 
                 GROUP BY r.id 
                 ORDER BY r.id DESC",
                esc_sql(sanitize_text_field($_GET['search'])),
                esc_sql(sanitize_text_field($_GET['search']))
            ),
            ARRAY_A
        );

        $total_items = count($registrations);
        $this->items = array_slice($registrations, $offset, $per_page);

    /**
     * Case 4: Standard listing (NO search, NO post filter)
     * Optimized:
     * - Use COUNT(*) for total items
     * - Use LIMIT / OFFSET to fetch only the current page
     * - No JOINs or GROUP BY needed
     */
    } else {

        // Fast total count
        $total_items = (int) $wpdb->get_var(
            "SELECT COUNT(*) FROM {$this->mollieForms->getRegistrationsTable()}"
        );

        // Fetch only the current page records
        $this->items = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT r.*
                 FROM {$this->mollieForms->getRegistrationsTable()} r
                 ORDER BY r.id DESC
                 LIMIT %d OFFSET %d",
                $per_page,
                $offset
            ),
            ARRAY_A
        );
    }

    // Pagination configuration for WP_List_Table
    $this->set_pagination_args([
        'total_items' => $total_items,
        'per_page'    => $per_page,
        'total_pages' => (int) ceil($total_items / $per_page),
    ]);
}