1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.