Support

March 11, 2024 at 5:11 pm

Greetings!

We previously were able to run the following SQL query to generate a table that contained all of our events, but events added recently are no longer showing… Seems to be something related to the join on the wp_esp_event_venue table… Did the schema change recently?

Thanks!!


SELECT SQL_CALC_FOUND_ROWS
wp_posts.post_title as Subject,
CONVERT(CONVERT_TZ(wp_esp_datetime.DTT_EVT_start,'Etc/UTC','America/Los_Angeles'), DATE) as StartDate,
CONVERT(CONVERT_TZ(wp_esp_datetime.DTT_EVT_start,'Etc/UTC','America/Los_Angeles'), TIME) as StartTime,
#wp_esp_datetime.DTT_EVT_start as Start,
CONVERT(CONVERT_TZ(wp_esp_datetime.DTT_EVT_end,'Etc/UTC','America/Los_Angeles'), DATE) as StopDate,
CONVERT(CONVERT_TZ(wp_esp_datetime.DTT_EVT_end,'Etc/UTC','America/Los_Angeles'), TIME) as StopTime,
#wp_esp_datetime.DTT_EVT_end as Stop,
CONCAT_WS(' ', wp_esp_venue_meta.VNU_address, wp_esp_venue_meta.VNU_city, 'CA', wp_esp_venue_meta.VNU_zip) as Location,
'FALSE' as AllDayEvent
FROM wp_posts
INNER JOIN wp_esp_datetime
ON wp_posts.ID = wp_esp_datetime.EVT_id
INNER JOIN wp_esp_event_meta
ON wp_posts.ID = wp_esp_event_meta.EVT_id
INNER JOIN wp_esp_event_venue
ON wp_posts.ID = wp_esp_event_venue.EVT_id
INNER JOIN wp_esp_venue_meta
ON wp_esp_event_venue.VNU_ID = wp_esp_venue_meta.VNU_ID
WHERE DTT_EVT_Start > CURDATE()
ORDER BY DTT_EVT_Start ASC