Events, occurrences, and series

Creating, reading, updating, and deleting Events in the Custom Tables (v1) implementation by example. Explaining The Events Calendar use of custom tables with examples.

Terminology

  • CT1 – Custom Tables v1. This is the current implementation of custom tables for Events and Occurrences.
  • iCalendar – An open standard for calendar and scheduling information.
  • Occurrence – An event that occurs at a specific date/time.
  • ORM – Object Relational Model. The ORM repository is an API within The Events Calendar and Event Tickets that simplifies the fetching of post objects.
  • Provisional post ID – An ID assigned to an occurrence so that it can be treated as a WP post.
  • Series – A custom post type where related events are grouped. Recurring Events have the Series post ID as their post_parent value.

Top ↑

Following along using wp shell

While not required at all, it’s helpful to follow the code examples trying them out on your WordPress development site of choice, thanks to the magic of wp-cli and its shell command.

Set up a development site with The Events Calendar (TEC) and The Events Calendar Pro (ECP), activate both plugins, and complete the existing Events migration, if required.

Install wp-cli and make sure it runs correctly using the wp cli info, it should show the version of wp-cli you are using. If all is well, then running the command to list active plugins, wp plugin list --status=active --fields=name,status should return something like this:

+---------------------+--------+
| name                | status |
+---------------------+--------+
| the-events-calendar | active |
| events-pro          | active |
+---------------------+--------+

You can now use the wp shell command to load a PHP REPL interpreter and try out the code examples. The shell will have pre-loaded WordPress and all the active plugins with it.

From here on, and in the following paragraphs, any code example should run in the shell provided by the wp shell command.

Make sure TEC and ECP are correctly loaded by checking if the tribe_events function exists:

wp> function_exists('tribe_events');
=> bool(true)

If the return value is not bool (true), then either TEC or ECP are not correctly loaded, get back and re-check the previous steps to fix this.

Top ↑

Creating and reading Events using the ORM

Everything must change for everything to remain the same.
Giuseppe Tomasi Prince of Lampedusa, from the movie “The Leopard”.

The API to create, read, update and delete (CRUD) Events at a programmatic level has one entry point: the tribe_events() function.

One of the objectives of the CT1 project was to avoid breaking back-compatibility with our existing APIs where possible, paying particular attention to the tribe_events() API. While some changes are unavoidable, most of tribe_events()‘s functionality did not change.

Let’s start by creating a single Event, an event that will occur once. If you’re following along using the shell provided by the wp shell command, enter the lines one by one, and press return to have the code in the line executed.

Note: the \ char you see in the code, as in the definition of the $args variable will tell the shell that you’re not done entering the command, and it should not execute when pressing return.

$args = [ \
    'title' => 'Test Single Event', \
    'status' => 'publish',  \
    'start_date' => '2022-09-01 10:00:00',  \
    'end_date' => '2022-09-01 12:00:00',  \
    'timezone' => 'America/New_York', \
];
$single_event = tribe_events()->set_args( $args )->create()->ID;

If everything went according to plan, the shell will print the ID of the newly created Event (the ID might differ);

wp> $single_event = tribe_events()->set_args( $args )->create()->ID;
=> int(1)

Let’s continue by creating a recurring Event; the Event will happen daily, 10 times:

$args = [ \
    'title' => 'Test Recurring Event', \
    'status' => 'publish', \
    'start_date' => '2022-09-01 14:00:00',  \
    'end_date' => '2022-09-01 17:00:00',  \
    'timezone' => 'America/New_York', \
    'recurrence' => 'RRULE:FREQ=DAILY;COUNT=10', \
];
$recurring_event = tribe_events()->set_args( $args )->create()->ID;

Note that we used the iCalendar RRULE format for the recurrence argument; compatibility with the iCalendar standard is one of the new features of the CT1 implementation and it can be used to replace the verbose recurrence format used in the _EventRecurrence post meta, if preferred.

If everything went fine the output should look like this (the ID might differ):

wp> $recurring_event = tribe_events()->set_args( $args )->create()->ID;
int(2)

Now we have created a Single Event and a Recurring Event, let’s try and read them back:

$ids = tribe_events()->where( 'start_date', '2022-09-01 10:00:00' )->pluck( 'ID' );

The output will look like this:

wp> $ids = tribe_events()->where( 'start_date', '>=', '2022-09-01 10:00:00' )->pluck( 'ID' );\

array(10) {
  [0] =>
  int(10000001)
  [1] =>
  int(10000002)
  [2] =>
  int(10000003)
  [3] =>
  int(10000004)
  [4] =>
  int(10000005)
  [5] =>
  int(10000006)
  [6] =>
  int(10000007)
  [7] =>
  int(10000008)
  [8] =>
  int(10000009)
  [9] =>
  int(10000010)
}

The output is not what we expected. This is one of the main differences between the CT1 implementation and typical WordPress behavior: for both Single and Recurring Events, there will always be a single post entry in the posts table. The list of post IDs we got back is a list of the Occurrences’ provisional post IDs, not the IDs of the Event posts themselves.

Top ↑

Occurrences’ provisional post IDs

In the CT1 implementation, the Event is a template for its Occurrences. A Single Event will occur once, a Recurring Event will occur multiple times (in our example: daily, 10 times). Running a direct database query will skip the query filters applied by the CT1 implementation and will return the IDs of the real Event posts that are in the database:

global $wpdb;
$wpdb->get_col( "SELECT ID FROM $wpdb->posts WHERE POST_TYPE = 'tribe_events'" );
wp> global $wpdb;
wp> $wpdb->get_col("SELECT ID FROM $wpdb->posts WHERE POST_TYPE = 'tribe_events'");
array(2) {
  [0] =>
  string(1) "1"
  [1] =>
  string(1) "2"
}

Since WordPress and its ecosystem is geared to work with post objects, and we only have 2 Event posts in our database, we need to provide (hence the name “provisional ID“) a way for Occurrences to be treated as post objects. Let’s get the provisional ID of the one Occurrence of the Single Event (in my installation the post ID of the Single Event is 1):

$single_occurrence = tribe_events()->where( 'ID', 1 )->first()->ID;

The output, in my installation, is the following:

wp> $single_occurrence = tribe_events()->where( 'ID', 1 )->first()->ID;
int(10000001)

The CT1 implementation rerouted the request for an Event by its real post ID to its first, and only, Occurrence provisional ID.

Let’s do the same with the Recurring Event (in my installation the post ID of the Recurring Event is 2):

$recurring_occurrence = tribe_events()->where( 'ID', 2 )->first()->ID;

The output:

wp> $recurring_occurrence = tribe_events()->where( 'ID', 2 )->first()->ID;
int(10000002)

Again: I got the first Occurrence of the Event post with ID 2. Can I get the provisional ID of another Occurrence? Say the third one: the one on 2022-09-03 14:00:00?

$third_occurrence_id = tribe_events()->where( 'ID', 2 )->where( 'start_date', '2022-09-03 14:00:00' )->first()->ID;

Again, CT1 will redirect the query to return the provisional ID of the third Occurrence:

wp> $third_occurrence_id = tribe_events()->where( 'ID', 2 )->where( 'start_date', '2022-09-03 14:00:00' )->first()->ID;
=> int(10000004)

A provisional ID will behave, for all intents and purposes, like a real post ID. We can get the post object, meta, and fields using an Occurrence provisional ID. Try out the following commands:

get_post_meta( 10000004, '_EventStartDate', true );
get_post( 10000004 )->post_title;

Depending on your installation state, your output might differ, but it should look like this:

wp> get_post_meta( 10000004, '_EventStartDate', true );
=> string(19) "2022-09-03 14:00:00"

wp> get_post( 10000004 )->post_title;
=> string(20) "Test Recurring Event"

The takeaway is that, in the CT1 implementation, you will be dealing with Occurrences, not with Events. Rest assured, provisional IDs will pass almost every check that is not a direct database query.

On the technical side, the CT1 implementation pre-fills WordPress caches with the Occurrences data. WordPress will first hit the cache when trying to get the post for a post ID, post meta values, or a post’s terms. Pre-filling those caches, in place of letting WordPress go and try to read the information regarding a non-existing post ID, will make Occurrences look like real posts.

Top ↑

Series

The concept of Series is not new to the CT1 implementation, but it’s representing a different concept. In the CT1 implementations, Series are containers for Events. All Events, Single and Recurring, that make sense together from an organizer and editor’s point of view can be grouped in a Series.

While Single Events can optionally be part of a Series, Recurring Events must be part of a Series. So much so that the ORM will automatically create a Series when creating a recurring Event. Let’s try and get the Series associated with the Single and Recurring Event:

$single_event_post_id = 1;
$recurring_event_post_id = 2;
$single_event_series = tec_series()->where( 'event_post_id', $single_event_post_id )->first();
$recurring_event_series = tec_series()->where( 'event_post_id', $recurring_event_post_id )->first()->ID;

The output:

wp> $single_event_series = tec_series()->where( 'event_post_id', $single_event_post_id )->first();
=> NULL

wp> $recurring_event_series = tec_series()->where( 'event_post_id', $recurring_event_post_id )->first()->ID;
=> int(3)

Notice that, to fetch the Series information from the database, we used the tec_series function. Similarly to the tec_events function, the tec_series function will return an instance of the ORM dedicated to Series.

The creation, contextual to the creation or update of an Event, of a Series, can be controlled using the series argument. In the following example, we will create a Series called “Test Series” and associate it with a Single Event. After that we’ll fetch the Series ID from the database leveraging its relation with the Single Event, and then create a Recurring Event part of the same Series:

$single_args = [ 
    'title' => 'Single Event', \
    'status' => 'publish', \
    'start_date' => '2022-09-01 14:00:00',  \
    'end_date' => '2022-09-01 17:00:00',  \
    'timezone' => 'America/New_York', \
    'series' => 'Test Series', \
];
$single_event = tribe_events()->set_args( $single_args )->create()->ID;
$series_id = tec_series()->where( 'event_post_id', $single_event)->first()->ID;
echo get_post( $series_id )->post_title;

$recurring_args = [ \
    'title' => 'Recurring Event', \
    'status' => 'publish', \
    'start_date' => '2022-09-01 14:00:00', \
    'end_date' => '2022-09-01 17:00:00',  \
    'timezone' => 'America/New_York', \
    'recurrence' => 'RRULE:FREQ=DAILY;COUNT=10', \
    'series' => $series_id, \
];

$recurring_event = tribe_events()->set_args( $recurring_args )->create()->ID;
$series_id = tec_series()->where( 'event_post_id', $recurring_event)->first()->ID;

The output (omitting the output generated by the definition of the $args variables):

wp> $single_args = [ ... ];
wp> $single_event = tribe_events()->set_args( $single_args )->create()->ID;
=> int(4)
wp> $series_id = tec_series()->where( 'event_post_id', $single_event)->first()->ID;
=> int(5)
wp> echo get_post( $series_id )->post_title;
Test Series
wp> $recurring_args = [ ... ];
wp> $recurring_event = tribe_events()->set_args( $recurring_args )->create()->ID;
=> int(6)
wp> $series_id = tec_series()->where( 'event_post_id', $recurring_event)->first()->ID;
=> int(5)

The two Events are part of the same Series: the one called “Test Series” with an ID of 5.

Using the provisional IDs of the Single and Recurring Event Occurrences will have the same effect:

$single_event_occurrence = tribe_events()->where( 'id', $single_event )->first()->ID;
$series_id = tec_series()->where( 'event_post_id', $single_event_occurrence)->first()->ID;
$recurring_event_third_occurrence = tribe_events()->where( 'id', $recurring_event )->first()->ID;
$series_id = tec_series()->where( 'event_post_id', $recurring_event_third_occurrence )->first()->ID;

Top ↑

Series replace post_parent

In the previous implementation, the post_parent field was used to relate a Recurring Event with its child Occurrences; a Recurring Event was a post representing (usually) the Event’s first Occurrence, and the subsequent Occurrences were posts related to the first by their post_parent field.

In the CT1 implementation, this relation is now represented by three entities and two relationships:

  • Series are related to Events (a 1-to-many relationship)
  • Events are related to Occurrences (a 1-to-many relationship)

This is not just a language change but a semantic change. In the previous implementation, a user could modify a single Occurrence of a Recurring Event or a set composed of an Occurrence and all the ones temporally following it. When this happened, the post_parent relationship would be broken, and users would be in the hard spot of having to remember “what goes together” or have to resort to “hacks” based on categories, tags, or other metadata. In the CT1 implementation, when one of the updates above happens, the broken-out (the result of an update to a single Occurrence) or the split (the result of an update to a set of Occurrences) will keep being related to the original Event by means of their shared Series relationship.

In practice, this means that developers should move from “fetch the next Occurrence of the Recurring Event after today” to “fetch the next Occurrence of the Series the Recurring Event belongs to that happens after today”.

With reference to the example Single and Recurring Event created before, we can write:

$series_id = tec_series()->where( 'event_post_id', $recurring_event)->first()->ID;
$next_occurrence_id = tribe_events()->where( 'series', $series_id )->where( 'starts_after', 'now' )->first()->ID;

The provisional ID returned will allow us to get all the Occurrence details we require.

Top ↑

The custom tables used by the CT1 implementation

As the name “Custom Table v1” implies, the CT1 implementation uses custom tables to store the Event data. It’s out of the scope of this documentation to explain the internal structure of the tables used by the CT1 implementation; it will evolve in shape as we extend the functionality of the plugin, and developers willing to interact programmatically with The Events Calendar and The Events Calendar PRO should consider the ORMs (tribe_events, tec_series and so on) the API to use when interacting with the database.

For the sake of completeness, the following tables are used by the CT1 implementation (the table prefix is omitted):

  • tec_events: the table that stores the Event data, each Single or Recurring Event will have a row in this table; many queries that used to JOIN on the posts meta table are now being redirected to this one. While most fields of this table are obvious, the start_date and end_date ones might be confusing: they represent the start date of the first Occurrence of the Event and the end date of the last Occurrence of the Event.
  • tec_occurrences: each time a Single or Recurring Event is created or updated, or its start date and time, end date and time, or recurrence and exclusion rules are updated, this table is updated accordingly. If, in the previous implementation, we used to query the posts and postmeta to find Events fitting a set of criteria, we now use this table to find Occurrences of Events fitting the same criteria.
  • tec_series_relationships: this table stores the 1-to-many relationship between Events and Series.

Internally, under the ORM API, the CT1 implementation uses the concept of “Models” based on both existing posts (like the Event and Series models) and entities that are not reflected in posts but are required by the implementation, like Relationships (between Series and Events) and Occurrences.