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
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
If so, try this:
- Ditch the subquery and resolve your
meta.meta_keyfilter using COALESCE
- Use the MIN function on
- If needed, CAST
meta.meta_valueas 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
Not sure if this is needed but it causes no harm. Just think of it as wearing a belt and suspenders.