Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
GROUP BY post_id in each table separately, producing a derived table and then join all these tables to posts. –  ypercube Jul 1 '13 at 21:47
    
See this similar question: Multiple “GROUP_CONCAT”s with WHERE clause –  ypercube Jul 1 '13 at 21:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.