Badges DB Views

This is a sub-page of the Overall DB Design for Badges

Diagram

badge_views.png

Matchup View to Entity

ID View Entity
1 achievement_type <indirect>
2 badge_post_top_level <indirect>
3 badge_features BadgeFeatures
4 badge_in_progress <indirect>
5 completed_badge Badge
6 step_class_rollup Step
7 badge_achievement_by_user Achievement

Review of existing queries

  • First performed July 27-28, 2019
  • Reviewed after BadgeOS version 3 added separate table for achievements.

Active

Top Level Badges

This supports the list of Top Level Badges.

  • achievement_type - This matches well with the list of Achievement Types we've been working with. It matches the menu, and matches what I would expect. A few caveats:
    • The Example Use Case Achievement Type isn't public. Can be excluded by limiting the records to those published after May 1, 2019.
    • Each Type covers one or more Top-Level badges. This needs to be joined against all posts to get the list of top-level badges.
  • badge_post_top_level - Appears to give us the correct list of top-level Badge posts. The IDs should be what we want for expanding trees of the posts to layout the criteria.
  • badge_features - Currently supplies the BadgeFeatures class in Java that ties together both the structure of a Badge (Criteria below) and what has been earned so far by the member. A few caveats and structural notes:
    • The list of posts derives from badge_post_top_level which itself comes from achievement_type.

Awarded Badges

Should be able to join the new wp_badgeos_achievements for a given user with badge_post_top_level.

In Progress Badges

  • Class BadgeProgress is currently assembled from multiple pieces and this will change as we switch from wp_usermeta to wp_badgeos_achievements.
  • badge_in_progress outer joins wp_badgeos_achievements with badge_post_top_level to obtain the steps toward badges in progress.
  • badge_achievement_by_user is a join between the Badge/Step tree (step_class_rollup) and the list of achievement steps for each user (wp_badgeos_achievements).

Criteria

This supports the tree structure for listing the achievements that lead toward earning the Top Level Badges.

  • posts_with_sub_achievements - This is currently supporting the BadgeStepsEntity Java class. The view is simply a list of the parent posts which have steps — some are for Top Level badges and some are for the intermediate level roll-ups. The BadgeStepsEntity links to:
    • BadgeFeatures class which carries details about the badge itself.
    • Step(Entity) List of the steps which make up the Badge.
  • step_class_rollup - Supports the Java class Step via the Entity class StepEntity. The key to understanding this is to recognize the difference between a Step's Class and a Step's Instance. The Step's Class is the Sub-Achievement type — the post that describes the Sub-Achievement. The Step's Instance is recorded when a particular badge requires a particular Sub-Achievement with a distinct name. For example, Tiny Door may be the class and Krog Tunnel Tiny Door may be the instance. Each such pair also carries the Badge or Rollup that is earned by completing the Step.

Unsure

Deprecated / Deleted

* achievement_meta - I'm not sure if I will use this, but it serves as an example of Transposition.
* sub_achievement - Looks similar to sub_achievement_with_parent, while carrying more columns, but haven't chased this one down.
* unlock_events - It appears that BadgeOS is moving away from this method of recording awards and is moving toward recording the data in wp_usermeta instead.

API Support

For each of the APIs on the Award Badges API page, we list out the queries and views that support the API.

The sub-headings below (Categories) align with the groups of APIs from the Award Badges API page.

Criteria

This is the static structure of what it takes to earn a badge.

  • This starts with a list of the top-level badges (badge_posts_top_level & badge_features)
  • It expands into the tree of child achievements required to earn the top-level badge.

Views

  • achievement_type - Early and simple query.
  • badge_with_meta - Transposition of Badge Data
  • badge_brief

Progress

Both Progress and Badge Brief depend on User's Badges

Badge Brief

Both Progress and Badge Brief depend on User's Badges

Send Events

Currently Active Views

As of July 28, 2019

Finding achievement types

  • achievement_type
SELECT
    `p`.`ID` AS `post_id`,
    `p`.`post_title` AS `post_title`,
    `p`.`post_name` AS `post_name`,
    `p`.`guid` AS `url`,
    `thumbp`.`guid` AS `image_url`
FROM
    (
        (
            `wp-cr`.`wp_posts` `p`
        JOIN `wp-cr`.`wp_postmeta` `pm`
        ON
            (
                (
                    (`pm`.`post_id` = `p`.`ID`) AND(`pm`.`meta_key` = '_thumbnail_id')
                )
            )
        )
    JOIN `wp-cr`.`wp_posts` `thumbp`
    ON
        ((`pm`.`meta_value` = `thumbp`.`ID`))
    )
WHERE
        (`p`.`post_type` = 'achievement-type') 
AND (`p`.`post_status` = 'publish') 
AND (`p`.`post_date` > '2019-05-01')
  • Find the records in wp_posts for post_type = "achievement-type"
  • Join on id = post_id to the wp_postmeta table
  • Join the _thumbnail_id attribute's value back to the wp_posts table to find the guid
  • Restricts the date range so the "Use Case Examples" from August 2018 are not included.

For list of Badge Posts

  • badge_post_top_level
SELECT p.*
  FROM wp_posts p
WHERE
        (`p`.`post_status` = 'publish') 
AND `p`.`post_type` IN (
        SELECT
            `achievement_type`.`post_name`
        FROM
            `wp-cr`.`achievement_type`
)
  • This is a list of all the posts records for Top-Level badges which could be awarded and are published in Clue Ride.

Badge Features

  • badge_features
SELECT
    `p`.`ID` AS `post_id`,
    `p`.`post_title` AS `post_title`,
    `p`.`post_type` AS `badge_name`,
    `p`.`post_name` AS `badge_level`,
    `p`.`guid` AS `base_url`,
    `thumbp`.`guid` AS `image_url`
FROM
    (
        (
            `wp-cr`.`badge_post_top_level` `p`
        LEFT JOIN `wp-cr`.`wp_postmeta` `pm`
        ON
            (
                (
                    (`pm`.`post_id` = `p`.`ID`) AND(`pm`.`meta_key` = '_thumbnail_id')
                )
            )
        )
    LEFT JOIN `wp-cr`.`wp_posts` `thumbp`
    ON
        ((`pm`.`meta_value` = `thumbp`.`ID`))
    )
WHERE
    (`p`.`post_status` = 'publish')
  • post_title is what shows up as the achievement page's title.
  • post_name is the slug (or perma-link) which is editable under "quick edit". I'm using the aware, adept, advocate, angel labels for these, although those names are sometimes combined with a more specific name for the badge.

Step / Class Rollup

For each Step, what is its "class" and the Badge it supports.

SELECT
    `parent_step`.`ID` AS `step_id`,
    `parent_step`.`post_title` AS `step_name`,
    `p`.`ID` AS `class_id`,
    `p`.`post_title` AS `class_name`,
    `parent`.`ID` AS `badge_id`,
    `parent`.`post_title` AS `badge_name`
FROM
    (
        (
            (
                (
                    `wp-cr`.`wp_posts` `p`
                LEFT JOIN `wp-cr`.`wp_p2p` `p2s`
                ON
                    ((`p2s`.`p2p_from` = `p`.`ID`))
                )
            LEFT JOIN `wp-cr`.`wp_posts` `parent_step`
            ON
                ((`parent_step`.`ID` = `p2s`.`p2p_to`))
            )
        LEFT JOIN `wp-cr`.`wp_p2p` `p2p`
        ON
            (
                (`p2p`.`p2p_from` = `parent_step`.`ID`)
            )
        )
    JOIN `wp-cr`.`wp_posts` `parent`
    ON
        ((`parent`.`ID` = `p2p`.`p2p_to`))
    )
WHERE
    (
        (`p`.`post_type` = 'sub-achievement') AND(
            (`parent_step`.`post_type` = 'step') OR(
                `parent_step`.`post_type` = 'sub-achievement'
            )
        )
    )
ORDER BY
    `parent`.`ID`,
    `p`.`ID`

Steps toward a Badge in Progress

badge_in_progress

SELECT
    `badge`.`ID` AS `badge_id`,
    `ach`.`entry_id` AS `entry_id`,
    `ach`.`ID` AS `ID`,
    `ach`.`post_type` AS `post_type`,
    `ach`.`achievement_title` AS `achievement_title`,
    `ach`.`rec_type` AS `rec_type`,
    `ach`.`points` AS `points`,
    `ach`.`point_type` AS `point_type`,
    `ach`.`user_id` AS `user_id`,
    `ach`.`this_trigger` AS `this_trigger`,
    `ach`.`image` AS `image`,
    `ach`.`site_id` AS `site_id`,
    `ach`.`date_earned` AS `date_earned`
FROM
    (
        `wp-cr`.`wp_badgeos_achievements` `ach`
    LEFT JOIN `wp-cr`.`badge_post_top_level` `badge`
    ON
        ((`badge`.`ID` = `ach`.`ID`))
    )
WHERE
    ISNULL(`badge`.`ID`)

Completed Badges

completed_badge

SELECT
    `badge`.`post_id` AS `post_id`,
    `badge`.`post_title` AS `post_title`,
    `badge`.`badge_name` AS `badge_name`,
    `badge`.`badge_level` AS `badge_level`,
    `badge`.`base_url` AS `base_url`,
    `badge`.`image_url` AS `image_url`,
    `ach`.`entry_id` AS `entry_id`,
    `ach`.`ID` AS `ID`,
    `ach`.`post_type` AS `post_type`,
    `ach`.`achievement_title` AS `achievement_title`,
    `ach`.`rec_type` AS `rec_type`,
    `ach`.`points` AS `points`,
    `ach`.`point_type` AS `point_type`,
    `ach`.`user_id` AS `user_id`,
    `ach`.`this_trigger` AS `this_trigger`,
    `ach`.`image` AS `image`,
    `ach`.`site_id` AS `site_id`,
    `ach`.`date_earned` AS `date_earned`
FROM
    (
        `wp-cr`.`wp_badgeos_achievements` `ach`
    JOIN `wp-cr`.`badge_features` `badge`
    ON
        ((`badge`.`post_id` = `ach`.`ID`))
    )

Badge Achievement by User

badge_achievement_by_user
Join of badge_in_progress against Step Badge Rollup with excluded records that match completed_badges for each user.

SELECT
    `ach`.`user_id` AS `user_id`,
    `sc`.`badge_id` AS `badge_id`,
    `sc`.`badge_name` AS `badge_name`,
    `sc`.`step_id` AS `step_id`,
    `sc`.`step_name` AS `step_name`,
    `sc`.`class_id` AS `class_id`,
    `sc`.`class_name` AS `class_name`,
    `ach`.`entry_id` AS `entry_id`,
    `ach`.`ID` AS `ID`,
    `ach`.`post_type` AS `post_type`,
    `ach`.`achievement_title` AS `achievement_title`,
    `ach`.`rec_type` AS `rec_type`,
    `ach`.`points` AS `points`,
    `ach`.`point_type` AS `point_type`,
    `ach`.`this_trigger` AS `this_trigger`,
    `ach`.`image` AS `image`,
    `ach`.`site_id` AS `site_id`,
    `ach`.`date_earned` AS `date_earned`
FROM
    (
        (
            `wp-cr`.`badge_in_progress` `ach`
        JOIN `wp-cr`.`step_class_rollup` `sc`
        ON
            ((`sc`.`step_id` = `ach`.`ID`))
        )
    LEFT JOIN `wp-cr`.`completed_badge` `cb`
    ON
        (
            (
                (`cb`.`user_id` = `ach`.`user_id`) AND(`cb`.`badge_id` = `sc`.`badge_id`)
            )
        )
    )
WHERE
    ISNULL(`cb`.`date_earned`)
ORDER BY
    `ach`.`user_id`,
    `sc`.`badge_id`
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License