Fix WPGraphQL cursor pagination when TEC Custom Tables V1 is active

class-teccursorfixer.php

<?php
/**
 * Fix WPGraphQL cursor pagination when TEC Custom Tables V1 is active.
 *
 * TEC Custom Tables V1 replaces WP_Meta_Query with Custom_Tables_Meta_Query,
 * redirecting meta key JOINs (e.g. _EventStartDate) from wp_postmeta to the
 * tec_occurrences table.
 *
 * This class addresses three issues:
 *
 * 1. Cursor SQL column: WPGraphQL's cursor builder defaults to
 *    wp_postmeta.meta_value, but that table is no longer JOINed.
 *    → Redirects to tec_occurrences.<column> via graphql_post_object_cursor_meta_key.
 *
 * 2. Cursor comparison value: WPGraphQL reads the cursor value via
 *    get_post_meta(), but TEC Custom Tables V1 may not keep wp_postmeta
 *    in sync after updates. The authoritative value is in tec_occurrences.
 *    → Fetches the correct value via graphql_cursor_ordering_field.
 *
 * 3. Cursor ID tiebreaker: WPGraphQL uses wp_posts.ID as a tiebreaker
 *    when sort values are equal. TEC Pro provisional IDs (~10M) make
 *    this comparison always true for real post IDs, causing the cursor
 *    WHERE clause to include the cursor row itself. This consumes the
 *    single over-fetch row, so hasNextPage is always false.
 *    → Replaces wp_posts.ID with tec_occurrences.occurrence_id via
 *      graphql_cursor_ordering_field.
 */

namespace GQL;

defined( 'ABSPATH' ) || die( 'Direct script access disallowed.' );

use TEC\Events\Custom_Tables\V1\WP_Query\Redirection_Schema;

/**
 * TECCursorFixer class
 */
class TECCursorFixer {

	/**
	 * Cache of redirection map to avoid repeated lookups.
	 *
	 * @var array|null
	 */
	private static $map_cache = null;

	/**
	 * Constructor
	 */
	public function __construct() {
		// Fix 1: Redirect the cursor SQL column reference.
		add_filter(
			'graphql_post_object_cursor_meta_key',
			array( __CLASS__, 'redirect_cursor_meta_key' ),
			10,
			5
		);

		// Fix 2: Correct the cursor comparison value from tec_occurrences.
		// Fix 3: Replace the ID tiebreaker for provisional IDs.
		add_filter(
			'graphql_cursor_ordering_field',
			array( __CLASS__, 'fix_cursor_ordering_value' ),
			10,
			3
		);
	}

	/**
	 * Get the TEC redirection map (cached).
	 *
	 * @return array The meta key → table/column redirection map.
	 */
	private static function get_map() {
		if ( null === self::$map_cache ) {
			if ( ! class_exists( '\TEC\Events\Custom_Tables\V1\WP_Query\Redirection_Schema' ) ) {
				self::$map_cache = array();
				return self::$map_cache;
			}
			self::$map_cache = Redirection_Schema::get_filtered_meta_key_redirection_map();
		}
		return self::$map_cache;
	}

	/**
	 * Get the TEC provisional post ID base.
	 *
	 * TEC Pro uses "provisional IDs" for recurring event occurrences:
	 * provisional_id = base + occurrence_id
	 *
	 * @return int The provisional ID base, or 0 if not available.
	 */
	private static function get_provisional_base() {
		return (int) get_option( 'tec_custom_tables_v1_provisional_post_base_provisional_id', 10000000 );
	}

	/**
	 * Check if an ID is a TEC provisional post ID.
	 *
	 * @param int $id The ID to check.
	 *
	 * @return bool True if the ID is a provisional ID.
	 */
	private static function is_provisional_id( $id ) {
		$base = self::get_provisional_base();
		return $id > $base;
	}

	/**
	 * Convert a provisional ID to an occurrence_id.
	 *
	 * @param int $provisional_id The provisional post ID.
	 *
	 * @return int The occurrence_id, or 0 if not a provisional ID.
	 */
	private static function get_occurrence_id( $provisional_id ) {
		$base = self::get_provisional_base();
		return $provisional_id > $base ? $provisional_id - $base : 0;
	}

	/**
	 * Redirect cursor meta key references to TEC custom tables when applicable.
	 *
	 * @param string $key       The SQL column key (e.g. wp_postmeta.meta_value).
	 * @param string $meta_key  The meta key name (e.g. _EventStartDate).
	 * @param string $meta_type The meta type.
	 * @param string $order     The order direction.
	 * @param object $cursor    The PostObjectCursor instance.
	 *
	 * @return string The corrected SQL column key.
	 */
	public static function redirect_cursor_meta_key( $key, $meta_key, $meta_type, $order, $cursor ) {
		$map = self::get_map();

		if ( empty( $map ) || ! isset( $map[ $meta_key ] ) ) {
			return $key;
		}

		$table  = $map[ $meta_key ]['table'];
		$column = $map[ $meta_key ]['column'];

		return "{$table}.{$column}";
	}

	/**
	 * Fix cursor ordering fields for TEC Custom Tables V1.
	 *
	 * Handles two cases via the graphql_cursor_ordering_field filter:
	 *
	 * Fix 3 – ID tiebreaker: WPGraphQL adds `wp_posts.ID < cursor_offset`
	 * as a tiebreaker when sort values are equal. TEC Pro provisional IDs
	 * (~10 000 000) are always greater than any real wp_posts.ID, so the
	 * comparison is always true. This makes the cursor WHERE inclusive of
	 * the cursor row, consuming the single over-fetch row and causing
	 * hasNextPage to always be false.
	 * → Replaces wp_posts.ID with tec_occurrences.occurrence_id.
	 *
	 * Fix 2 – Comparison value: WPGraphQL reads the cursor value via
	 * get_post_meta(). TEC Custom Tables V1 may not keep wp_postmeta in
	 * sync; the authoritative value lives in tec_occurrences.
	 * → Fetches the correct value from tec_occurrences.
	 *
	 * @param array  $field          The cursor field (key, value, type, order).
	 * @param object $cursor_builder The CursorBuilder instance.
	 * @param object $object_cursor  The PostObjectCursor instance (nullable).
	 *
	 * @return array The corrected field array.
	 */
	public static function fix_cursor_ordering_value( $field, $cursor_builder, $object_cursor ) {
		if ( ! is_array( $field ) || empty( $field['key'] ) ) {
			return $field;
		}

		$map = self::get_map();
		if ( empty( $map ) ) {
			return $field;
		}

		// Fix 3: Correct the ID tiebreaker for provisional IDs.
		if ( isset( $field['type'] ) && 'ID' === $field['type'] ) {
			$id_value = (int) $field['value'];
			if ( self::is_provisional_id( $id_value ) ) {
				$first_entry = reset( $map );
				if ( $first_entry ) {
					$field['key']   = $first_entry['table'] . '.occurrence_id';
					$field['value'] = (string) self::get_occurrence_id( $id_value );
				}
			}
			return $field;
		}

		// Fix 2: Replace cursor comparison value with authoritative value.
		if ( ! $object_cursor || ! isset( $object_cursor->cursor_offset ) || ! $object_cursor->cursor_offset ) {
			return $field;
		}

		// Check if the key matches a TEC-redirected column.
		foreach ( $map as $meta_key => $target ) {
			$table_col = "{$target['table']}.{$target['column']}";
			if ( $field['key'] !== $table_col ) {
				continue;
			}

			// Fetch the authoritative value from tec_occurrences.
			// Handle TEC Pro provisional IDs (base + occurrence_id).
			global $wpdb;
			$cursor_id = $object_cursor->cursor_offset;

			if ( self::is_provisional_id( $cursor_id ) ) {
				$occurrence_id = self::get_occurrence_id( $cursor_id );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$value = $wpdb->get_var(
					$wpdb->prepare(
						'SELECT `' . esc_sql( $target['column'] )
						. '` FROM `' . esc_sql( $target['table'] )
						. '` WHERE occurrence_id = %d LIMIT 1',
						$occurrence_id
					)
				);
			} else {
				$order_dir = ( ! empty( $field['order'] ) && 'DESC' === strtoupper( $field['order'] ) )
					? 'DESC' : 'ASC';
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$value = $wpdb->get_var(
					$wpdb->prepare(
						'SELECT `' . esc_sql( $target['column'] )
						. '` FROM `' . esc_sql( $target['table'] )
						. '` WHERE post_id = %d ORDER BY `' . esc_sql( $target['column'] ) . '` '
						. esc_sql( $order_dir )
						. ' LIMIT 1',
						$cursor_id
					)
				);
			}

			if ( null !== $value ) {
				$field['value'] = $value;
			}
			break;
		}

		return $field;
	}
}