Paginated custom post type results fail to load using v1.14.10 and onwards

[mensch]

Description

I have a WordPress installation running WP GraphQL and The Events Calendar, using GraphQL queries to populate a paginated listing of events. This was working well until the release of v1.14.10, now only the initial query succeeds and when the next cursor is queried it fails with the following error output in the debug.log:

WordPress database error Unknown column 'mt0.meta_value' in 'where clause' for query 
			SELECT   wp_posts.ID
			FROM wp_posts  INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )  INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id )
			WHERE 1=1  AND ( 
  wp_postmeta.meta_key = '_EventStartDate' 
  AND 
  ( mt1.meta_key = '_EventEndDate' AND CAST(mt1.meta_value AS DATETIME) >= '2023-08-25 10:52:51' )
) AND wp_posts.post_type = 'tribe_events' AND ((wp_posts.post_status = 'publish')) AND  mt0.meta_value >= "2023-08-29 00:00:00" AND ( mt0.meta_value > "2023-08-29 00:00:00" OR (  wp_posts.post_date >= "2023-06-23 14:00:31" AND ( wp_posts.post_date > "2023-06-23 14:00:31" OR (  wp_posts.ID > 28403 ) )  ) ) 
			GROUP BY wp_posts.ID
			ORDER BY CAST(wp_postmeta.meta_value AS CHAR) ASC, wp_posts.post_date ASC, wp_posts.ID DESC 
			LIMIT 0, 11
		 made by require('wp-blog-header.php'), wp, WP->main, WP->parse_request, do_action_ref_array('parse_request'), WP_Hook->do_action, WP_Hook->apply_filters, WPGraphQL\Router::resolve_http_request, WPGraphQL\Router::process_http_request, WPGraphQL\Request->execute_http, GraphQL\Server\StandardServer->executeRequest, GraphQL\Server\Helper->executeOperation, GraphQL\Server\Helper->promiseToExecuteOperation, GraphQL\GraphQL::promiseToExecute, GraphQL\Executor\Executor::promiseToExecute, GraphQL\Executor\ReferenceExecutor->doExecute, GraphQL\Executor\ReferenceExecutor->executeOperation, GraphQL\Executor\ReferenceExecutor->executeFields, GraphQL\Executor\ReferenceExecutor->resolveField, GraphQL\Executor\ReferenceExecutor->resolveFieldValueOrError, WPGraphQL\Utils\InstrumentSchema::WPGraphQL\Utils\{closure}, WPGraphQL\Type\WPConnectionType->WPGraphQL\Type\{closure}, WPGraphQL\Type\Connection\PostObjects::WPGraphQL\Type\Connection\{closure}, WPGraphQL\Data\DataSource::resolve_post_objects_connection, WPGraphQL\Data\Connection\AbstractConnectionResolver->get_connection, WPGraphQL\Data\Connection\AbstractConnectionResolver->execute_and_get_ids, WPGraphQL\Data\Connection\PostObjectConnectionResolver->get_query, WP_Query->__construct, WP_Query->query, WP_Query->get_posts
[25-Aug-2023 08:59:42 UTC] WordPress database error Unknown column 'mt0.meta_value' in 'where clause' for query 
			SELECT   wp_posts.ID
			FROM wp_posts  INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )  INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id )
			WHERE 1=1  AND ( 
  wp_postmeta.meta_key = '_EventStartDate' 
  AND 
  ( mt1.meta_key = '_EventEndDate' AND CAST(mt1.meta_value AS DATETIME) >= '2023-08-25 10:59:42' )
) AND wp_posts.post_type = 'tribe_events' AND ((wp_posts.post_status = 'publish')) AND  mt0.meta_value >= "2023-08-29 00:00:00" AND ( mt0.meta_value > "2023-08-29 00:00:00" OR (  wp_posts.post_date >= "2023-06-23 14:00:31" AND ( wp_posts.post_date > "2023-06-23 14:00:31" OR (  wp_posts.ID > 28403 ) )  ) ) 
			GROUP BY wp_posts.ID
			ORDER BY CAST(wp_postmeta.meta_value AS CHAR) ASC, wp_posts.post_date ASC, wp_posts.ID DESC 
			LIMIT 0, 11
		 made by require('wp-blog-header.php'), wp, WP->main, WP->parse_request, do_action_ref_array('parse_request'), WP_Hook->do_action, WP_Hook->apply_filters, WPGraphQL\Router::resolve_http_request, WPGraphQL\Router::process_http_request, WPGraphQL\Request->execute_http, GraphQL\Server\StandardServer->executeRequest, GraphQL\Server\Helper->executeOperation, GraphQL\Server\Helper->promiseToExecuteOperation, GraphQL\GraphQL::promiseToExecute, GraphQL\Executor\Executor::promiseToExecute, GraphQL\Executor\ReferenceExecutor->doExecute, GraphQL\Executor\ReferenceExecutor->executeOperation, GraphQL\Executor\ReferenceExecutor->executeFields, GraphQL\Executor\ReferenceExecutor->resolveField, GraphQL\Executor\ReferenceExecutor->resolveFieldValueOrError, WPGraphQL\Utils\InstrumentSchema::WPGraphQL\Utils\{closure}, WPGraphQL\Type\WPConnectionType->WPGraphQL\Type\{closure}, WPGraphQL\Type\Connection\PostObjects::WPGraphQL\Type\Connection\{closure}, WPGraphQL\Data\DataSource::resolve_post_objects_connection, WPGraphQL\Data\Connection\AbstractConnectionResolver->get_connection, WPGraphQL\Data\Connection\AbstractConnectionResolver->execute_and_get_ids, WPGraphQL\Data\Connection\PostObjectConnectionResolver->get_query, WP_Query->__construct, WP_Query->query, WP_Query->get_posts

A portion of this is also outputted on the GraphQL endpoint.

It seems the generated SQL query is requesting mt0.meta_value, but that table is never defined (mt1 is). The issue probably stems from the recent changes to PostObjectCursor in this merged pull request, as assigning aliases to the metadata tables is handled in src/Data/Cursor/PostObjectCursor.php around line 229.

As tribe_events is a post type introduced by The Events Calendar, I've amended its configuration as follows:

/**
 * Add events to GraphQL collection
 *
 * @param array $args
 * @param string $post_type
 * @return array
 */
add_filter( 'register_post_type_args', function($args, $post_type) {
    if ($post_type === 'tribe_events') {
        $args['show_in_graphql'] = true;
        $args['graphql_single_name'] = 'tribeEvent';
        $args['graphql_plural_name'] = 'tribeEvents';
        $args['graphql_connections'] = [ 'tribeEventsCategory' ];
        $args['graphql_fields'] = [
            'start_time' => [
                'type' => 'String',
                'resolve' => function( $event ) {
                    $event = tribe_get_event($event->databaseId);
                    return $event->start_date;
                }
            ],
            'recurring' => [
                'type' => 'String',
                'resolve' => function( $event ) {
                    $event = tribe_get_event($event->databaseId);
                    if ($event->recurring) {
                        $meta = get_post_meta($event->ID, '_EventRecurrence', true);
                        if (isset($meta['rules'][0]['custom']['type']) && $meta['rules'][0]['custom']['type'] === 'Monthly') {
                            return __('Maandelijks evenement', 'bvhl');
                        }

                        if (isset($meta['rules'][0]['custom']['type']) && $meta['rules'][0]['custom']['type'] === 'Weekly') {
                            return __('Wekelijks evenement', 'bvhl');
                        }
                    }
                }
            ],
            'schedule_details' => [
                'type' => 'String',
                'resolve' => function( $event ) {
                    $event = tribe_get_event($event->databaseId);
                    return strip_tags($event->schedule_details->value());
                }
            ],
        ];
    }

    return $args;
}, 10, 2 );

I've added the entire PHP file containing the relevant GraphQL actions and filter. I'm not sure what other information would be required to debug, but I'd be happy to provide it.

Steps to reproduce

1. Perform a paginated query on the frontend using Vue.js or via the IDE in the WordPress admin
2. An example query would be:

query getTribeEvents($where: RootQueryToTribeEventConnectionWhereArgs!) {
  tribeEvents(where: $where, first: 10, after: "YXJyYXljb25uZWN0aW9uOjI2NzEy") {
    pageInfo {
      hasNextPage
      hasPreviousPage
      startCursor
      endCursor
    }
    nodes {
      id
      title
      excerpt
      link
      featuredImage {
        node {
          guid
        }
      }
      recurring
      start_time
      schedule_details
      tribeEventsCategories {
        nodes {
          name
        }
      }
    }
  }
}

But a simpler paginated query fails as well.
3. The query fails with a fatal error preventing any meaningful GraphQL output.

Additional context

No response

WPGraphQL Version

1.14.10 and higher

WordPress Version

6.3

PHP Version

8.0.3

Additional enviornment details

• Roots Sage
• VueJS + Apollo Client
• The Events Calendar + Pro
• Registrations for the Events Calendar Pro
• Advanced Custom Fields Pro

Please confirm that you have searched existing issues in the repo.

• Yes

Please confirm that you have disabled ALL plugins except for WPGraphQL.

• Yes
• My issue is with compatibility with a specific WordPress plugin, and I have listed all my installed plugins (and version info) above.

[justlevine]

Thanks for reporting this @mensch !

Would you mind taking #2882 for a spin and letting us know if it addresses your issue?

There's still a few edge cases that need to be addressed in that PR, but afaik those are preexisting bugs and not regressions stemming from 1.14.10 🤞

[mensch]

Thanks for your quick reply. I've just checked out that PR, but unfortunately the error is still generated upon request.

[jasonbahl]

@mensch we've pushed up additional commits to the #2882 branch over the past few days. Can you check out the latest and try again?

You might also need a new version of "QL Events" (https://github.com/the-events-calendar/ql-events). (@kidunot89, can you confirm?)

[mensch]

@jasonbahl Thanks for the update. Just checked out the latest changes of that branch and my issue seems to be resolved! I'm not using QL Events by the way.

[jasonbahl]

@mensch sweet! Thanks for confirming.

It's been merged and released. Will close this issue.