Writing a plugin for Wordpress using a custom post type and an additional alias table to hold repetition of the post events. The problem comes with duplicate post meta values.
wp_post_meta table:
meta_id | post_id | meta_key | meta_value
--------+---------+--------------------+---------------------
2748 | 5456 | calendar_venue_id | 5
2749 | 5456 | calendar_start_date| 2013-06-28 21:13:00
2750 | 5456 | calendar_end_date | 2013-06-28 22:13:00
2751 | 5456 | calendar_link | null
2752 | 5456 | calendar_recur | D
2753 | 5456 | _edit_last | 1
2754 | 5456 | _edit_lock | 1372460794:1
2755 | 5456 | calendar_repeat_on | 16
2756 | 5456 | calendar_repeats | 1
2757 | 5456 | calendar_recur_end | 1
wp_post table:
ID | post_author | post_date | post_date_gmt | post_content | post_title | post_excerpt | post_status | post_type
-----+-------------+---------------------+---------------------+---------------+----------------------+--------------+-------------+----------
5456 | 1 | 2013-06-28 21:48:18 | 2013-06-28 21:48:18 | werk w/ me!!! | come one alias table | null | publish | event
wp_calendar_alias
alias_event | alias_venue | alias_start_date | alias_end_date
------------+-------------+---------------------+---------------------
5456 | 5 | 2013-06-28 21:06:00 | 2013-06-28 22:06:00
5456 | 5 | 2013-06-28 21:13:00 | 2013-06-28 22:13:00
5456 | 5 | 2013-06-29 21:06:00 | 2013-06-29 22:06:00
5456 | 5 | 2013-06-29 21:13:00 | 2013-06-29 22:13:00
5456 | 5 | 2013-06-30 21:06:00 | 2013-06-30 22:06:00
5456 | 5 | 2013-06-30 21:13:00 | 2013-06-30 22:13:00
5456 | 5 | 2013-07-01 21:07:00 | 2013-07-01 22:07:00
5456 | 5 | 2013-07-01 21:13:00 | 2013-07-01 22:13:00
5456 | 5 | 2013-07-02 21:07:00 | 2013-07-02 22:07:00
5456 | 5 | 2013-07-02 21:13:00 | 2013-07-02 22:13:00
5456 | 5 | 2013-07-03 21:07:00 | 2013-07-03 22:07:00
5456 | 5 | 2013-07-03 21:13:00 | 2013-07-03 22:13:00
5456 | 5 | 2013-07-04 21:07:00 | 2013-07-04 22:07:00
5456 | 5 | 2013-07-04 21:13:00 | 2013-07-04 22:13:00
5456 | 5 | 2013-07-05 21:07:00 | 2013-07-05 22:07:00
5456 | 5 | 2013-07-05 21:13:00 | 2013-07-05 22:13:00
5456 | 5 | 2013-07-06 21:07:00 | 2013-07-06 22:07:00
5456 | 5 | 2013-07-06 21:13:00 | 2013-07-06 22:13:00
5456 | 5 | 2013-07-07 21:13:00 | 2013-07-07 22:13:00
5456 | 5 | 2013-07-08 21:13:00 | 2013-07-08 22:13:00
5456 | 5 | 2013-07-09 21:13:00 | 2013-07-09 22:13:00
5456 | 5 | 2013-07-10 21:13:00 | 2013-07-10 22:13:00
MYSQL QUERY:
SELECT GROUP_CONCAT(DISTINCT postmeta.meta_key ORDER BY postmeta.meta_key ASC) AS post_meta_keys,
GROUP_CONCAT(postmeta.meta_value ORDER BY postmeta.meta_key ASC) AS post_meta_values,
GROUP_CONCAT(DISTINCT alias.alias_start_date ORDER BY alias.alias_start_date ASC) AS alias_start_dates,
GROUP_CONCAT(DISTINCT alias.alias_end_date ORDER BY alias.alias_start_date ASC) AS alias_end_dates,
posts.*,
venues.*
FROM wp_posts AS posts
LEFT JOIN wp_postmeta AS postmeta ON posts.ID = postmeta.post_id
LEFT JOIN wp_calendar_alias AS alias ON posts.ID = alias.alias_event
LEFT JOIN wp_calendar_venues AS venues ON alias.alias_venue = venues.venue_id
WHERE posts.ID = 5456 AND posts.post_type = 'event'
GROUP BY postmeta.post_id
The query gives me a single row result but the post_meta_values repeats the same values. post_meta_keys would as well except I applied distinct to it. The problem is that post_meta_values may have same meta_value but for different meta_values.
I am still a MYSQL novice so I am not sure what to do. It looks like it applies the alias results to every post_meta result. Not sure how to make it not do that.
Thanks, Anthony
