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):

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