General Procedure For Transposing Postmeta
  1. Start with generating a list of post IDs that contain the meta data you want to analyze.
  2. Then, list out the fields that are defined for that post type.
  3. 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):

  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

 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

  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
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License