How do I create an overview page with custom calculations from a post-type?

Question

I've created some custom post-types using the Pods framework, however the the solution to my question should be the same regardless of pods. My custom types are event, team, achievement, achievementunlock. Each team and achievement belongs to an event, each team has a number of users assigned, and each user can post an achievementunlock and choose from a list of achievements.

I'd like to create a page which displays an overview of which achievements have been "unlocked", i.e. a user has posted an achievementunlock post for.

Something like this

Event1Name
                  | Team1            |Team2
                  | Bob   | Steve    | Scott  | Mary  |
Achievement1Name  | View  | -        | -      | View  |
Achievement2Name  | -     | View     | -      | View  |
Achievement3Name  | -     | -        | View   |       |
Total             | 1     | 1        | 1      | 2     |
Team Total        | 2                | 3              |

Event2Name
.....

I'd need to be able to iterate all objects and see their properties. I.e. An achievement post is posted by Bob, I need to be able to also see which team he belongs to, and which event that team belongs to, etc.

How can I fetch my objects which I can iterate over them with ease, and how do I setup such a page in wordpress? Thanks!

Answer 1

It seems you are asking for a complete custom funcionality,

To add menu, you can use this.

function wpdocs_register_my_custom_menu_page() {
    add_menu_page(
        __( 'Custom Menu Title', 'textdomain' ),
        'custom menu',
        'manage_options',
        'myplugin/myplugin-admin.php',
        'custom_callback_function',
        plugins_url( 'myplugin/images/icon.png' ),
        6
    );
}
add_action( 'admin_menu', 'wpdocs_register_my_custom_menu_page' );

function custom_callback_function(){

echo 'Here is my page contents';

}

https://developer.wordpress.org/reference/functions/add_menu_page/

You can use $wpdb query class for custom functionalities

https://codex.wordpress.org/Class_Reference/wpdb

Hopes this will give you basic idea about this.

Answer 2

Although Rahilesh pointed me in the right direction and is upvoted for that, I feel that I can provide a more complete answer now that I've got things running.

First create a new PHP template file, create a page and connect it to that template and add the page to your menu.

In that file you can now query the wordpress databases using the $wpdb object as Rajilesh suggested. You can access any of the wordpress tables. Data on each post is in posts, and anything added through the Pods framework is found in postmeta where each item is has a postID and meta_key which is set in your Pods settings.

For example:

$posts = $wpdb->get_results(
    " SELECT * FROM $wpdb->posts WHERE 
     (post_type = 'event' OR
     post_type = 'team'");

Note: This will include all posts, so it may be a good idea check include some checks to avoid trashed items and drafts in your SQL query as such:

post_status <> 'draft' AND
post_status <> 'auto-draft' AND
post_status <> 'trash'
etc...

Some basic data fields use the name directly, while relationship and media fields use a _pods_ prefix. Relationships will be arrays which hold the ID of their partner.

$meta = $wpdb->get_results("SELECT * FROM $wpdb->postmeta WHERE 
                            meta_key = '_pods_unlocks' OR
                            meta_key = 'achievement' OR

If your setup is a bit complex as mine was, it may be a good idea to build an object oriented approach and create objects for each of your Pods.

Now that you've gained access to all your data, you can output it in any way you see fit with custom code such as:

usort($event->Teams, "sortTeams");
echo "<h3>";
for ($i = 0; $i < count($event->Teams); $i++)
{
    $team = $event->Teams[$i];
    echo ($i + 1) . ": $team->Title ($team->Score)<br/>";
}
echo "</h3>";

Hopefully this helps someone along the way!