Fix for GP Entry Blocks Slow Query

gistfile1.txt

add_filter('gpeb_filters_col_rows_query', 'custom_optimize_gf_entry_meta_query_with_cache', 10, 3);

function custom_optimize_gf_entry_meta_query_with_cache($sql, $col, $table) {
    // Check if the current table and column are the ones we want to optimize
    if ($table === GFFormsModel::get_entry_meta_table_name() && $col === 'meta_key') {
        // Attempt to retrieve cached meta_key results
        $cached_meta_keys = get_transient('gf_entry_meta_keys');

        // If the cache exists, skip running the query
        if ($cached_meta_keys !== false) {
            // echo '<p>Cache HIT: Using cached meta_key results.</p>';
            // Return a dummy query that returns the cached result (no actual execution needed)
            return 'SELECT "cached_value"';
        } else {
            // echo '<p>Cache MISS: Executing the meta_key query and setting cache.</p>';
            global $wpdb;
            
            // Execute the query to get the distinct meta keys
            $start_time = microtime(true);
            $meta_keys = $wpdb->get_col($sql);
            $execution_time = microtime(true) - $start_time;

            // echo "<p>Execution time for gpeb_filters_col_rows_query: {$execution_time} seconds</p>";
            // echo "<p>Number of meta keys: " . count($meta_keys) . "</p>";

            // If results are found, set the transient for 12 hours
            if (!empty($meta_keys)) {
                set_transient('gf_entry_meta_keys', $meta_keys, 12 * HOUR_IN_SECONDS);
                // echo '<p>Cache set for gf_entry_meta_keys.</p>';
            } else {
                // echo '<p>No meta keys found; cache not set.</p>';
            }

            // Return the original SQL since we need to populate the cache
            return $sql;
        }
    }

    // Return the original SQL for all other queries
    return $sql;
}

add_filter('gpeb_filters_col_rows', 'use_cached_gf_meta_keys', 10, 3);

function use_cached_gf_meta_keys($rows, $col, $table) {
    // Check if we are dealing with the meta_key column of the entry meta table
    if ($table === GFFormsModel::get_entry_meta_table_name() && $col === 'meta_key') {
        // Attempt to retrieve cached meta_key results
        $cached_meta_keys = get_transient('gf_entry_meta_keys');

        if ($cached_meta_keys !== false) {
            // echo '<p>Using cached result for gf_entry_meta_keys.</p>';
            return $cached_meta_keys;
        } else {
            // echo '<p>Cache not found; executing the original query.</p>';
        }
    }

    return $rows;
}

add_action('init', 'reset_offset_on_filter_apply');

function reset_offset_on_filter_apply() {
    // Check if the filter form is submitted and filters are present
    if (isset($_GET['filters_form_id']) && !empty($_GET['filters'])) {
        // Check if 'offset' exists in the query parameters and remove it
        if (isset($_GET['offset'])) {
            $query_args = $_GET;
            unset($query_args['offset']);
            
            // Rebuild the query string without 'offset'
            $new_query_string = http_build_query($query_args);
            
            // Get the base URL (without the query string)
            $base_url = strtok($_SERVER["REQUEST_URI"], '?');

            // Rebuild the URL correctly
            $new_url = $base_url . '?' . $new_query_string;

            // Redirect to the URL without the 'offset' parameter
            wp_redirect(esc_url_raw($new_url));
            exit;
        }
    }
}