Support

Home Forums Event Espresso Premium mySQL Query For Event List Broken After Update

mySQL Query For Event List Broken After Update

Posted: March 11, 2024 at 5:11 pm

Viewing 5 reply threads


Eric Powers

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


Rio

  • Support Staff

March 21, 2024 at 7:24 pm

Hello,

Sorry for the delay, We have new update 5.0.19 is now available, do you experience any problem using that version?

thanks


Eric Powers

March 21, 2024 at 8:54 pm

Greetings!

I updated to latest version and the issue persists.

Let me know if there’s anything else I can provide to assist with troubleshooting.

Thank you!


Tony

  • Support Staff

March 22, 2024 at 5:38 am

Hi Eric,

Yes, the schema changed in version 5.0.0+

esp_event_venue is no longer used to save the Event Venue relations, they all save within esp_event_meta now. We have a migration script which copies the ‘old’ values from esp_event_venue over for existing events, see:

\event-espresso-core\core\data_migration_scripts\5_0_0_stages\EE_DMS_5_0_0_Event_Venues.dmsstage.php

New events store the relationship directly within esp_event_meta.


Eric Powers

March 22, 2024 at 8:50 am

Thanks! That relationship update fixed my issue.

Now, how do I run the EE_DMS_5_0_0_Event_Venues.dmsstage.php ? I tried calling it from bash using php \event-espresso-core\core\data_migration_scripts\5_0_0_stages\EE_DMS_5_0_0_Event_Venues.dmsstage.php but it says I’m missing a dependency…?

Thank you again!


Tony

  • Support Staff

March 23, 2024 at 7:40 am

Did it not run when you initially updated event espresso?

Or have you altered the version numbers on your install? (Ask as your running your own custom queries so something I’ve seen previously is people patching their own version numbers, which will affect migrations)

Viewing 5 reply threads

The support post ‘mySQL Query For Event List Broken After Update’ is closed to new replies.

Have a question about this support post? Create a new support post in our support forums and include a link to this existing support post so we can help you.

Event Espresso