Need event query examples

[therealgilles]

It would be great to provide event query examples and show which ones work and which ones do not quite work yet.

Examples could include: • events happening today • events happening now • events between start/end dates • events happening from today onwards • events happening from now onwards • events that happened before today • events that happened before now • events happening on a specific date with timezone • events between start/end dates with only first instance of recurring events

[justlevine]

You're looking for the startDateQuery and endDateQuery on the where filter.

The following would get you all the events happening for the first week of December, 2020 for example.

 events(
    where: { 
      startDateQuery: { after: { year: 2020, month: 12, day: 1} },
      endDateQuery: { before: { year: 2020, month: 12, day: 8}
    }
    nodes{
      id
      uri
    }

If you want today/now onwards, leave off the endDateQuery; if you want only past events then leave off the startDateQuery. note: if you need to show any past events you must set endDateQuery{ before } to something.

Obviously, you wouldnt hardcode those in the gql query, but rather figure out what you want and pass them as gql variables.

myQuery( $startYear: Int, $startMonth: Int, $startDay: Int) {
    events( where: { startDateQuery: { after: { year: $startYear, month: $startMonth, day: $startDay} } } )
}

For exact dates, you can skip the before/after syntax and just pass use {start|end}DateQuery { year $startYear, month: $startMonth, day: $startDay } . This will work for past events too.

Same with the timezone: you'd convert it to the actual date you save your events in first.

As far as recurring events go, I havnt had a chance to play around with it all yet. But Im 60% sure that the default behavior is to only fetch the first instance, with recurring instances stored in event { eventDates }.

[therealgilles]

Hi @justlevine, thank you for your answers.

1) When I try the first query, past events are not included. I only get future events (i.e. events after 'now'). Is that a bug or would something be not quite right in my setup?

2) What's the difference between dateQuery and startDateQuery? how do I know which one to use?

3) What's the difference between the day/month/year under dateQuery/(start|end)DateQuery and the ones under before/after?

Thanks for the help :)

[justlevine]

Shoot, this is what happens when I try to code from memory on my phone.

1. Not sure if its a bug or intentional behavior (i believe it's the same in tribe_events() ), but the only way to see past events if you're not passing an exact date explicitly set an endDateQuery: {before...}.

2. That's my bad (was typing on my phone from memory). you want to be using startDateQuery. dateQuery is for the publish date, not the event date.

3. using day/month/year will return on that specific date (you need all three). Otherwise use before and after. My bad for using one day as the example, I was just trying to illustrate how to handle a range.

Going to go back and edit my original comment now 😅

[therealgilles]

Thanks for the correction. I tried this:

  events(
    where: {
        startDateQuery: { after: { day: 1, month: 11, year: 2020 } },
        endDateQuery: { before: { day: 15, month: 12, year: 2020 } }
    }
  )
  {
    nodes {
      startDate
      endDate
      recurring
    }
  }

and got the following result:

"data": {
    "events": {
      "nodes": [
        {
          "startDate": "2021-06-16 04:00:00",
          "endDate": "2021-06-17 03:59:59",
          "recurring": true
        },
        {
          "startDate": "2021-09-01 21:30:00",
          "endDate": "2021-09-01 23:45:00",
          "recurring": true
        },
        {
          "startDate": "2021-01-02 19:00:00",
          "endDate": "2021-01-03 19:00:00",
          "recurring": false
        },
        {
          "startDate": "2021-02-22 09:00:00",
          "endDate": "2021-02-22 10:00:00",
          "recurring": true
        },
        {
          "startDate": "2020-12-30 20:00:00",
          "endDate": "2020-12-30 20:45:00",
          "recurring": true
        },
        {
          "startDate": "2020-12-27 16:15:00",
          "endDate": "2020-12-27 17:05:00",
          "recurring": true
        },
        {
          "startDate": "2020-12-26 09:00:00",
          "endDate": "2020-12-26 10:00:00",
          "recurring": false
        },
        {
          "startDate": "2020-12-27 11:00:00",
          "endDate": "2020-12-27 11:00:00",
          "recurring": false
        }
      ]
    }
  }

so something is off (for me at least). All the events returned are outside the date range.

[justlevine]

Seems you're running into the same issue I did a few weeks back.

Try making the following change to the plugin: https://github.com/simplur/ql-events/issues/19#issuecomment-738173446

[therealgilles]

Thanks @justlevine, will give it a try. What's the best way to find the WP queries that wp-graphql is running to get the result?

[justlevine]

Best way? no idea, but it probably involves xdebug.

What I've been doing is filtering graphql_connection_query with an error_log( print_r( $query, true ) ); return $query. Similarly with graphql_connection_query_args

[therealgilles]

So here is the query I want:

SELECT SQL_CALC_FOUND_ROWS  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 = '_EventEndDate' AND wp_postmeta.meta_value > '2020-11-01' ) 
  AND 
  ( mt1.meta_key = '_EventStartDate' AND mt1.meta_value < '2020-12-15' )
) AND wp_posts.post_type = 'tribe_events' AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value ASC LIMIT 0, 10

and here is the query wp-graphql runs:

            SELECT
                SQL_CALC_FOUND_ROWS *
            FROM (
                SELECT  DISTINCT wp_posts.*, wp_postmeta.meta_value as 'EventStartDate' 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 )  INNER JOIN wp_postmeta AS mt2 ON ( wp_posts.ID = mt2.post_id ) LEFT JOIN wp_postmeta as tribe_event_end_date ON ( wp_posts.ID = tribe_event_end_date.post_id AND tribe_event_end_date.meta_key = '_EventEndDate' )  WHERE 1=1  AND ( 
  wp_postmeta.meta_key = '_EventStartDate' 
  AND 
  ( mt1.meta_key = '_EventStartDate' AND CAST(mt1.meta_value AS DATE) > '2020-11-01' ) 
  AND 
  ( mt2.meta_key = '_EventEndDate' AND CAST(mt2.meta_value AS DATE) < '2020-12-15' )
) AND wp_posts.post_type = 'tribe_events' AND ((wp_posts.post_status = 'publish')) AND (wp_postmeta.meta_value >= '2020-12-21 23:34:39' OR (wp_postmeta.meta_value <= '2020-12-21 23:34:39' AND tribe_event_end_date.meta_value >= '2020-12-21 23:34:39' )) ORDER BY wp_posts.post_date DESC, wp_posts.ID DESC 
            ) a
            GROUP BY IF( post_parent = 0, ID, post_parent )

            LIMIT 0, 11

Here is the issue: This part (... AND tribe_event_end_date.meta_value >= '2020-12-21 23:34:39' )) is contradicting my query. Not sure why this is added.

PS: I am not sure how to order by event start date. Would appreciate guidance.

[therealgilles]

I think I found the issue. TEC has a posts_where filter in the-events-calendar/src/Tribe/Query.php that automatically set query parameters if the query has start_date/end_date set, and somehow start_date is set at that point. Now to figure out how it gets set.

[therealgilles]

Okay found it. If eventDisplay is not set, the pre_get_posts function in the same file sets the following:

          $query->set( 'hide_upcoming', $maybe_hide_events );
          $query->set( 'start_date', date_i18n( Tribe__Date_Utils::DBDATETIMEFORMAT ) );
          $query->set( 'orderby', self::set_orderby( null, $query ) );
          $query->set( 'order', self::set_order( null, $query ) );

I think the solution is to set eventDisplay to 'custom'.

[therealgilles]

I confirm that works. Now to figure out how to order by startDate or endDate. I don't quite see how to specify a custom orderby field in wp-graphql. I think it's mentioned here.

[therealgilles]

Continuing on this quest, the better solution may be to set 'tribe_suppress_query_filters' to true in the query. This disables all the query manipulation done by the TEC plugin. In which case, it's not necessary to set eventDisplay anymore.

[therealgilles]

Here are my findings so far: • The TEC (and TEC pro) plugins have filters that modify the graphql query and cause issues. • tribe_suppress_query_filters can be set to false in the query to disable the TEC (and TEC pro) filters. • The downside of disabling the filters is that the code that controls hiding subsequent recurrences of the same event is also disabled. • Graphql queries will most likely work without disabling the filters as long as eventDisplay is set to 'custom' in the query and 'Recurring event instances' (corresponding to hide subsequent recurrences) is not checked in the TEC settings. • The TEC pro code to hide subsequent recurrences does not modify the graphql query quite right. Most of the code is in the collapse_sql function under events-calendar-pro/src/Tribe/Recurrence/Queries.php.

I am wondering if it's better to either: A) Set tribe_suppress_query_filters to false by default and maybe replicate some of the 'hide subsequent recurrences' code in the ql-events plugin to avoid losing that functionality, with an additional query variable to control it. OR B) Only set eventDisplay to 'custom' and hope that the TEC (and TEC pro) filters won't interfere with the graphql query in the future, and patch up the query for when 'hide subsequent recurrences' is set.

Thoughts or advice are welcome :)

[therealgilles]

I abandoned the idea of [B] because the TEC Pro plugin collapsesql function to implement the 'hide subsequent recurrences' looks to me like a really bad hack. It's running regex search/replace on the SQL SELECT query and then adding an (unnecessary) second SELECT query on top, when it could have used the existing posts(distinct|fields|groupby) filters to achieve the same thing, and without the second SELECT.

I was able to make [A] work. The code looks something like this in get_query_args in ql-events/includes/data/connection/class-event-connection-resolver.php:

        /**
         * Merge the input_fields with the default query_args
        */
        if ( ! empty( $input_fields ) ) {
            $query_args = array_merge( $query_args, $input_fields );
        }

        // disable the Events Calendar query filters
        $query_args['tribe_suppress_query_filters'] = true;

        if (isset($query_args['firstRecurrenceOnly']) && ($query_args['firstRecurrenceOnly'] == true)) {
            add_filter( 'posts_distinct', array( __CLASS__, 'posts_distinct' ) );
            add_filter( 'posts_groupby', array( __CLASS__, 'posts_groupby' ) );
            add_filter( 'posts_pre_query', array( __CLASS__, 'remove_posts_query_filters' ) );
        }

        return apply_filters(
            'graphql_' . Main::POSTTYPE . '_connection_query_args',
            $query_args,
            ...

and then:

    public static function remove_posts_query_filters () {
        remove_filter( 'posts_distinct', array( __CLASS__, 'posts_distinct' ) );
        remove_filter( 'posts_groupby', array( __CLASS__, 'posts_groupby' ) );
        remove_filter( 'posts_pre_query', array( __CLASS__, 'remove_posts_query_filters' ) );
        return null;
    }

    public static function posts_distinct ( $distinct ) {
        return 'DISTINCT';
    }

    public static function posts_groupby ( $groupby ) {
        global $wpdb;
        return "IF( {$wpdb->posts}.post_parent = 0, {$groupby}, {$wpdb->posts}.post_parent )";
    }

I added firstRecurrenceOnly to where_args in class-events.php if tribe_is_recurring_event is callable (i.e. TEC Pro is loaded). Finally I added META to PostObjectsConnectionOrderbyEnum in wp-graphql/src/Type/Enum/PostObjectsConnectionOrderbyEnum.php with 'meta_value' as value.

I can now run the following query:

query MyQuery {
  events(where: {
    startDateQuery: {
      after: {
        day: 1, month: 12, year: 2020
      }
    },
    endDateQuery: {
      before: {
        day: 15, month: 12, year: 2020
      }
    },
    orderby: { field: META, order: ASC },
    firstRecurrenceOnly: true
  }, first: 30) {
    nodes {
      title
      startDate
    }
  }
}

It feels a bit weird to use META as orderby field, when it's not defined anywhere in the query right now. If anyone has a better idea, let me know.

I am going to look at search because that's not working for me right now, even with regular posts. The query looks like this as soon as I turn search on:

SELECT * FROM wp_posts WHERE 1=2

If anyone has a clue about this, let me know.

UPDATE: For the search, it looks like it's the relevanssi plugin that's interfering.

[justlevine]

MT is well aware of the hackiness of the hackiness involved in getting WP_Query to work right, and they suggest using the tribe ORM instead. For this reason I think disabling tribe_suppress_query_filters is the wrong approach; there's too many outliers this plugin would need to account for, and will likely lead to more breaking changes and unexpected behavior in the future.

SQL optimization is nowhere close to my forte, so I can't weigh in on the performance considerations for their extra SELECT, but if it really is unnecessary, then I think the better approach would be suggesting they implement a change (from experience, they've very receptive to contributions even in their Pro plugins) and not bypassing TEC with custom sql queries.

I think the best approach long-term would be switching the entire plugin over to the ORM, but it would require a major rework. On a smaller scale, I think your suggestions could already use the ORM instead of those add/remove filters... Do you mind sharing a fork with your changes?

[therealgilles]

Thanks @justlevine for your reply and sharing your thoughts. I totally forgot about the ORM route, mainly because I thought that WP-GraphQL was based on the WP_Query at its core. I'm new to GraphQL and WP-GraphQL. Am I getting the wrong impression? Also wouldn't using the TEC ORM be the same as using their REST API? i.e. you're only getting access to what they provide and you may get more data than what you really want?

And yes, I will create a fork. I've made a few changes since yesterday and will also update the code in the comment above.

PS: I'm reading discussions on the WP-GraphQL github about how it is currently too tied to Wordpress internals. One example is the search, which reproduces the 's' query. I also ran into this, having to use META for the orderby field. I'm mentioning this because I am wary of tying this extension too much to the TEC plugin implementation. It seems it would be better to define a clear schema for the events/venues/organizers/... in the database and use that to query the data. In my (current) opinion, that would bring it closer to the spirit of GraphQL. By extension, I am not sure that GraphQL should be so tied to the WP_Query (but I could be a misjudgment from my part).

[therealgilles]

Here is my fork with the changes: https://github.com/therealgilles/ql-events/tree/feature/firstRecurrenceOnly

[justlevine]

The ORM is just a wrapper for WP_Query, so you're still returning only the information you request. Im just like you - getting started with WP-GraphlQL, so things like query purity and the spirit of the project are better addressed by the project maintainers (both here and the wp-graphql project).

I will say that considering this plugin is inexorably linked to TEC, I dont see how practical in either the short or long term it would be to replicate their functionality. You already ran into one issue that required the clever yet complicated solution of enabling/disabling filters from the TEC plugin, wait til you get to the mutations for attendee registration or user-submitted events. The ORM doesnt just handle it all neatly, but its production tested by MT, which means no matter the edge case, the database calls will always return what is intended.

Regardless, the approaches arent mutually exclusive :-)

[therealgilles]

I see, I can buy into that.

Would that mean get_query should be implemented for events / venues / organizers / tickets / attendees... using the tribe ORM functions instead of relying on the Post type get_query function?

What downsides can you foresee if adopting the TEC ORM?

Something I'm thinking about: 'Hide subsequent recurrences' is a TEC plugin setting option. I would not want to have to change the option whether I want to query one way or the other. Hopefully it can be overridden when using the ORM.

[justlevine]

All that's beyond my paygrade. but in my mind, events should use tribe_events(), venues and organizers are straightforward CPTs to use a regular WP_Query, and tickets should be an interface (instead of the separate RSVP, Paypal and WooTickets type).

The possible downsides (need to do more research) would be the orm's caching mechanism (no idea how it interacts with gql, but regardless it can be disabled), sql overhead (like the extra SELECT you pointed out, but this isnt my forte... in PHP i've the orm to be just as performant as WP_Query), and extending the query with custom meta for beginners (dont think this is wp-graphql's user demographic, but surfacing a custom field in the ORM does require a few steps... could be addressed with a filter in ql-events).

Something I'm thinking about: 'Hide subsequent recurrences' is a TEC plugin setting option. I would not want to have to change the option whether I want to query one way or the other. Hopefully it can be overridden when using the ORM.

Should be as simple as filtering the query_vars['tribeHideRecurrence']

[therealgilles]

It does seem like the abstraction would be a good idea.

The ORM seems to cover venues/organizers too through tribe_venues() and tribe_organizers(), so why not use them too?

Curious how much ordering the ORM supports. I see '@todo @be here support more ordering criteria than date.' here. Looking at the page you pointed at, the query are all ANDs, so it's not clear how to implement an OR in there. UPDATE: I do see where_or and where_multi in there, so it's probably possible. Wondering if there is a complete ORM spec somewhere.

Should be as simple as filtering the query_vars['tribeHideRecurrence']

That's only possible if you have access to the query before it is run. Which I guess would be possible through filters, so okay.

[therealgilles]

@kidunot89: what do you think of re-writing the plugin using the TEC ORM?

To clarify, I am not asking you to do it. I am wondering if you have thoughts, feedback, objections... Thank you.