1

I've spent about 4 hours trying to figure out why I cannot do a group by of the post_title and left join the date data from the wp_postmeta table

The code without the group by clause, results all my events but I only want to retrieve the first instance of the event, by grouping by the post_title, which should give me the only first date in the string of classes to sign up for.

What am I doing wrong here?

SELECT posts.post_title,meta.date
    FROM wp_posts posts
        LEFT JOIN (
            SELECT meta_value AS date,post_id FROM wp_postmeta
            WHERE wp_postmeta.meta_key ='_EventStartDate'
            ) meta
        ON posts.ID = meta.post_ID
WHERE posts.post_type='Tribe_Events' AND posts.post_status = 'publish'
GROUP BY posts.post_title
Alex Bloom
  • 83
  • 1
  • 1
  • 7
  • Probably not the root cause of your issue but still consider using a different name for the "date" column. Reasons why found here... https://stackoverflow.com/questions/12775671/is-date-a-valid-mysql-column-name/12785154 – Jon Jaussi Dec 30 '20 at 08:02
  • In the absence of any aggregating functions, a GROUP BY clause is never appropriate – Strawberry Dec 30 '20 at 08:49
  • 1
    And in this instance, a subquery is unnecessary – Strawberry Dec 30 '20 at 08:50
  • For further help, see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query – Strawberry Dec 30 '20 at 08:51

1 Answers1

0

Currently meta.date is not in a GROUP BY, no math is performed on this field, and it is not part of a DISTINCT list of fields

It sounds like you want the first instance of wp_postmeta.meta_value for each wp_posts.post_title

If so, try this:

  1. Ditch the subquery and resolve your meta.meta_key filter using COALESCE
  2. Use the MIN function on meta.meta_value
  3. If needed, CAST meta.meta_value as a datetime

Something like this maybe...harder to know without seeing the table structure and sample data.

SELECT posts.post_title
      ,MIN(CAST(meta.meta_value as DATETIME)) event_start_date 
  FROM wp_posts posts
  LEFT JOIN wp_postmeta meta
         ON posts.ID = meta.post_ID
WHERE posts.post_type ='Tribe_Events' 
  AND posts.post_status = 'publish'
  AND COALESCE(meta.meta_key, 'UNKNOWN') ='_EventStartDate'
GROUP BY posts.post_title;

This version can cope with multiple instances of '_EventStartDate' per post_title

Not sure if this is needed but it causes no harm. Just think of it as wearing a belt and suspenders.

Jon Jaussi
  • 1,298
  • 3
  • 18
  • 36