No events listed, confusing SQL query mixing event-date and event-start-date

  • Resolved hallvors

    (@hallvors)


    6 years, 4 months ago

    Hi,
    events on a site I manage for a friend/customer have gone missing. On debugging I have narrowed things down to the generated query mixing event-date and event-start-date in a confusing way. The past events query look like this per the WP devtools plugin:

    SELECT SQL_CALC_FOUND_ROWS wp_site_posts.ID
    FROM wp_site_posts
    INNER JOIN wp_site_postmeta
    ON ( wp_site_posts.ID = wp_site_postmeta.post_id )
    INNER JOIN wp_site_postmeta AS mt1
    ON ( wp_site_posts.ID = mt1.post_id )
    WHERE 1=1
    AND ( wp_site_postmeta.meta_key = ‘event-start-date’
    AND ( ( mt1.meta_key = ‘event-date’
    AND CAST(mt1.meta_value AS SIGNED) < ‘1541203200’ ) ) )
    AND wp_site_posts.post_type = ‘event’
    AND ((wp_site_posts.post_status = ‘publish’))
    GROUP BY wp_site_posts.ID
    ORDER BY wp_site_postmeta.meta_value+0 DESC
    LIMIT 0, 10

    I’m not totally fluent in SQL but I find this part very odd:

    AND ( wp_site_postmeta.meta_key = ‘event-start-date’
    AND ( ( mt1.meta_key = ‘event-date’
    AND CAST(mt1.meta_value AS SIGNED) < ‘1541203200’ ) ) )

    Why does it look for booth event-start-date and event-date?

Viewing 7 replies - 1 through 7 (of 7 total)
  • Thread Starter hallvors

    (@hallvors)

    BTW: in the database, meta_key: event-date is used exclusively. These (maybe from an earlier plugin version?) appear as end dates in the control panel UI. I tried re-naming all to event-start-date but it did not help the events appear.

    Thread Starter hallvors

    (@hallvors)

    The fix was to create a duplicate event-start-date entry for all:

    insert into wp_site_postmeta (post_id, meta_key, meta_value) select post_id, ‘event-start-date’, meta_value from wp_site_postmeta where meta_key = ‘event-date’;

    Plugin Author Guido

    (@guido07111975)

    Hi,

    Yes, versions 4.0 and older didn’t have an event start date. So I assume your client used a very old version of my plugin and updated to a newer one? I thought this didn’t cause serious issues.. but apparently it does.

    Guido

    Thread Starter hallvors

    (@hallvors)

    Sounds likely. I’m afraid I don’t know exactly what version(s) were running at what point.

    Plugin Author Guido

    (@guido07111975)

    Hi,

    So apparently you’ve solved this issue and we cannot reproduce the actual cause of this, so I’m marking this thread as resolved.

    Guido

    Plugin Author Guido

    (@guido07111975)

    Hi @hallvors

    Did some research. Every shortcode from version 4.0 untill now uses the event start date in it’s query, so I’m almost certain that’s the reason why events without event start date weren’t listed after you updated from old version to current version.

    Guido

    Thread Starter hallvors

    (@hallvors)

    I hope this thread will be useful if anyone else comes across this issue at some point ??

Viewing 7 replies - 1 through 7 (of 7 total)
  • The topic ‘No events listed, confusing SQL query mixing event-date and event-start-date’ is closed to new replies.