Sub Achievement Metadata

Conventions

The record connecting a Badge to the elements making up the badge have multiple parts arranged as a hierarchical node tree:

Top Level Node

  • Post record of type that matches one of the Badge-specific achievement types.
  • Meta data for that Badge

Sub-Achievements

  • Post record of type that matches sub-achievement
  • Meta Data for that sub-achievement
  • A link (via many-to-many relationship recorded in the wp_p2p table) to a BadgeOS step
  • Meta Data for that step
  • Parent post of that Sub-Achievement

Rules

  • Name/Title of the Sub-Achievement will be past tense describing what was accomplished ("Entered Data", "Raised Exception")
  • Name/Title of the Step will be present tense imperative ("Enter Data", "Raise Exception")

Views

A diagram would be good here

Criteria

  • sub_achievements
  • badge_with_meta - Transposition of Badge Data
  • achievement_meta - Transposition of Sub Achievement data
  • achievement_type - Early and simple query.
  • step_meta - Transposition of Step meta data
  • badge_brief
  • sub_achievements_with_parent

Transposing Rows to Columns

General Procedure for Transposing PostMeta
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

For Sub-Achievements

SELECT 
  post_id,
  max(if (meta_key = '_badgeos_all_attachment_submission', meta_value, null)) as attachment_submission,
  max(if (meta_key = '_badgeos_credly_categories', meta_value, null)) as credly_categories,
  max(if (meta_key = '_badgeos_credly_expiration', meta_value, null)) as credly_expiration,
  max(if (meta_key = '_badgeos_credly_include_evidence', meta_value, null)) as credly_include_evidence,
  max(if (meta_key = '_badgeos_credly_include_testimonial', meta_value, null)) as credly_include_testimonial,
  max(if (meta_key = '_badgeos_credly_is_giveable', meta_value, null)) as credly_is_giveable,
  max(if (meta_key = '_badgeos_earned_by', meta_value, null)) as earned_by,
  max(if (meta_key = '_badgeos_hidden', meta_value, null)) as hidden,
  max(if (meta_key = '_badgeos_maximum_earnings', meta_value, null)) as maximum_earnings,
  max(if (meta_key = '_badgeos_points', meta_value, null)) as points,
  max(if (meta_key = '_badgeos_send_to_credly', meta_value, null)) as send_to_credly,
  max(if (meta_key = '_badgeos_sequential', meta_value, null)) as sequential,
  max(if (meta_key = '_edit_last', meta_value, null)) as edit_last,
  max(if (meta_key = '_edit_lock', meta_value, null)) as edit_lock,
  max(if (meta_key = '_thumbnail_id', meta_value, null)) as thumbnail_id
 
  FROM `wp_postmeta`
 where post_id in (
     select sub_achievement_with_parent.sub_achievement_post_id 
       from sub_achievement_with_parent
--      where parent_id = 3402
     )
 
 GROUP by post_id
 
-- meta_key    count(meta_key)    
-- _badgeos_all_attachment_submission    4    
-- _badgeos_credly_categories    11    
-- _badgeos_credly_expiration    11    
-- _badgeos_credly_include_evidence    11    
-- _badgeos_credly_include_testimonial    11    
-- _badgeos_credly_is_giveable    11    
-- _badgeos_earned_by    11    
-- _badgeos_hidden    11    
-- _badgeos_maximum_earnings    11    
-- _badgeos_points    5    
-- _badgeos_send_to_credly    11    
-- _badgeos_sequential    1    
-- _edit_last    11    
-- _edit_lock    11    
-- _thumbnail_id    11

For Badges

wp-clueride/wp-cr/wp_postmeta/ https://phpmyadmin.jettmarks.com/tbl_sql.php?db=wp-cr&table=achievement_meta
Showing rows 0 - 13 (14 total, Query took 0.0012 seconds.)

SELECT DISTINCT meta_key, count(meta_key)
FROM `wp_postmeta`
where post_id in (
select post_id from badge_brief
)
GROUP by meta_key

meta_key count(meta_key)
_badgeos_congratulations_text 1
_badgeos_credly_categories 2
_badgeos_credly_expiration 2
_badgeos_credly_include_evidence 2
_badgeos_credly_include_testimonial 2
_badgeos_credly_is_giveable 2
_badgeos_earned_by 2
_badgeos_hidden 2
_badgeos_maximum_earnings 2
_badgeos_points 1
_badgeos_send_to_credly 2
_edit_last 2
_edit_lock 2
_thumbnail_id 2

Queries

List of Meta Data Fields: Sub-Achievement

SELECT DISTINCT meta_key, count(meta_key) 
  FROM `wp_postmeta`
 where post_id in (
     select sub_achievement_with_parent.sub_achievement_post_id from sub_achievement_with_parent
     )
 GROUP by meta_key

Data

Field Name Occurrences
_badgeos_all_attachment_submission 4
_badgeos_credly_categories 11
_badgeos_credly_expiration 11
_badgeos_credly_include_evidence 11
_badgeos_credly_include_testimonial 11
_badgeos_credly_is_giveable 11
_badgeos_earned_by 11
_badgeos_hidden 11
_badgeos_maximum_earnings 11
_badgeos_points 5
_badgeos_send_to_credly 11
_badgeos_sequential 1
_edit_last 11
_edit_lock 11
_thumbnail_id 11

List of Meta Data Fields: Steps

Data

wp-clueride/wp-cr/wp_postmeta/        https://phpmyadmin.jettmarks.com/tbl_sql.php?db=wp-cr&table=wp_postmeta
 Showing rows 0 -  3 (4 total, Query took 0.0018 seconds.)
 
SELECT DISTINCT meta_key, count(meta_key) 
  FROM `wp_postmeta`
 where post_id in (
     select sub_achievement_with_parent.step_id from sub_achievement_with_parent
     )
 GROUP by meta_key
meta_key    count(meta_key)    
_badgeos_achievement_type    11    
_badgeos_count    11    
_badgeos_trigger_type    11    
_thumbnail_id    11

VIEW: Sub-Achievement with Parent

CREATE VIEW sub_achievement_with_parent AS
SELECT p.ID as sub_achievement_post_id,
       p.post_title as sub_achievement_name,
       parent_step.id as step_id,
       parent_step.post_title as step_name,
       parent.ID as parent_id,
       parent.post_title as parent_name
  FROM wp_posts p
  LEFT JOIN wp_p2p p2s
    ON p2s.p2p_from = p.ID
  LEFT JOIN wp_posts parent_step
    ON parent_step.ID = p2s.p2p_to
  LEFT JOIN wp_p2p p2p
    ON p2p.p2p_from = parent_step.ID
  LEFT JOIN wp_posts parent
    ON parent.ID = p2p.p2p_to    
 WHERE p.post_type = 'sub-achievement' 
   AND parent_step.post_type = 'step'
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License