building a wordpress api that calls multiple tables

TL;DR - How do I tie information together from different tables to create an API for a plugin.

To give a run down of what I am doing. We are building an app and a website for a clients gym, the app, for now, only pulls in blog posts, calendar activities, allows them to add the activities to their phone, and a description of the facility, while the website goes more in depth about the gym, the trainers, and any events going on. It was decided that we should use Wordpress for the admin panel for the website and the app, so they only need to do the work in one place.

For the app I am using WP REST API v2 to pull in the blog posts, which is the easy part. But the calendar has become extremely difficult. The theme we bought is Symetrio Gym and Fitness which comes with a calendar that we could use, and it almost matches what we were using in the app. So what we need to do is pull in the data that is entered into the calendar and use an API Call in the app to display the data.

So here is where things get complicated, the calendar uses multiple tables to pull in all it's data. For the Trainer, Classes, and Room it creates a custom post type, that just houses the name of the trainer, class, or room. But for the dates, and times, and the data that it references, it uses two separate tables, one called schedule_ap_multi that has the date, the reference id, and the id of the class, that is referenced in posts.

The next table is called schedule_ap_multi_fields, that has the start time, the end time, the id that references which row to use in schedule_ap_multi, the id of the room post, the id of the trainer, and a description of the activity, but these are all in their own row, the only thing really tying them together in the ID for schedule_ap_multi.

here are some images of how the two tables are set up.

schedule_api_multi

schedule_api_multi_fields

I started to build an API for the calendar, but I can't get any data from the tables, it seems I can only get data from Posts, and since the dates and start and end time are not posts, its difficult to get them. for the JSON object I am creating its structured like this:

 protected function make_data( $post, $data ) {

 global $wpdb;

 $classesID = $wpdb->get_results('SELECT ID FROM wp_wtr_schedule_ap_multi',ARRAY_A);
 $classes = $wpdb->get_results('SELECT * FROM wp_posts WHERE post_type = "classes"',OBJECT);

 $date = $wpdb->get_results('SELECT * FROM wp_wtr_schedule_ap_multi');
 $data[ $post->ID ] = array(
            'title'             => 'the title',
            'type'              => $post->post_type,
            'room'              => 'the room',
            'instructor'        => 'the instructor',
            'description'       => 'the description',
            'start_date'        => 'start date',
            'end_date'          => 'end date',
            'image'             => 'image',
            'style'             => 'style',
   };
  return $date;

when I do $post-> it only gets the post_type of posts, and it's been a challenge getting any other post_type that isn't a post, when I use any of the wpdb commands I get the query back, but in full, and only if I use it as one of the JSON objects, it won't return an array when I put it in

$data[ here ]=array()

What I am trying to do, to summarize, is tie all these objects together, to make one api call, hopefully, and fill in the calendar on the app side, to display the data I need for each activity created in WordPress. I am at a complete loss now to what I need to do, and no one else on my team has any experience with wordpress plugins or creating a custom API for wordpress.

via Chebli Mohamed