General Procedure For Transposing Postmeta
- Start with generating a list of post IDs that contain the meta data you want to analyze.
- Then, list out the fields that are defined for that post type.
- From there, you can create a transposition view using MAX(If(key=<>, value, null)) as key for the field definitions.
Generating a List of Post IDs
Example for certain Badge Events that denote unlocking a badge (or achievement):
SELECT id, post_author, post_date, post_title FROM `wp_posts` where post_type = 'badgeos-log-entry' AND post_title like '%unlocked%' ORDER BY `wp_posts`.`ID` DESC
This was worked up in SQL and then turned into a view unlock_events.
List out field definitions
wp-clueride/wp-cr/wp_postmeta/ https://phpmyadmin.jettmarks.com/tbl_sql.php?db=wp-cr&table=unlock_events Showing rows 0 - 0 (1 total, Query took 0.0018 seconds.) SELECT DISTINCT meta_key, count(meta_key) FROM `wp_postmeta` where post_id in ( select id from unlock_events ) GROUP by meta_key _badgeos_log_achievement_id 20
Workout Tranposition Query
This particular case was trivial since there was only a single field (_badgeos_log_achievement_id), so the example below is from a post type (step) that had 4 different fields:
For Steps
SELECT post_id, max(if (meta_key = '_badgeos_achievement_type', meta_value, null)) as achievement_type, max(if (meta_key = '_badgeos_count', meta_value, null)) as count, max(if (meta_key = '_badgeos_trigger_type', meta_value, null)) as trigger_type, max(if (meta_key = '_thumbnail_id', meta_value, null)) as thumbnail_id FROM `wp_postmeta` where post_id in ( select sub_achievement_with_parent.step_id from sub_achievement_with_parent ) GROUP by post_id
page revision: 1, last edited: 25 Aug 2018 23:09