How would I add custom tables/endpoints to the WP REST API?

Question

I have created custom tables for performance reasons for products, carts, and notifications. I want to make these tables and their data available for normal CRUD via the WP REST API v2. How would I add custom tables (and their columns) to the API so that I could get/update those records?

Answer 1

I eventualy worked out a solution for my restaurants table, which sits alongside the wp_* tables in my WP database. Hope this helps

add_action( 'rest_api_init', function () {
  register_rest_route( 'restos/v1', '/all', array(
    'methods' => 'GET',
    'callback' => 'handle_get_all',
    'permission_callback' => function () {
      return current_user_can( 'edit_others_posts' );
    }
  ) );
} );

function handle_get_all( $data ) {
    global $wpdb;
    $query = "SELECT qname, rname, recommendation FROM `restaurants`";
    $list = $wpdb->get_results($query);
    return $list;
}

I have variations on this theme for all the CRUD actions I need

Answer 2

Rather than create custom database tables, you would be better off creating your own custom post types (within Wordpress - Since 4.7.+), along with whatever custom fields you need (as well as taxonomies).

All this can be done as a plugin or via functions.php in a theme.

Here's a very simple but thorough example of how to do this.

It shows how to create a custom post type (called 'acme_products') and its custom fields with the ability for custom taxonomies (categories). All with REST API support built in. It will also show you how to customise the input (meta) boxes for the WP-Admin forms and the columns of the list pages.

https://gist.github.com/kosso/47004c9fa71920b441f3cd0c35894409

Answer 3

It has just worked with the rest api v1 for me. The v2 declined the callback permission for selecting the table results. Here is an example endpoint for fetching from custom db table: domain.com/wp-json/test/v1/testing

add_action( 'rest_api_init', function () {
        $namespace = 'test/v1';
        $route     = '/testing';

        register_rest_route($namespace, $route, array(
            'methods'   => WP_REST_Server::READABLE,
            'callback'  => function () {
                global $wpdb;
                $table = $wpdb->prefix . 'test';
                $query = "SELECT name, color, etc.. FROM $table";
                $results = $wpdb->get_results($query);
                return $results;
            },
            'permission_callback' => '__return_true',
            'args' => array()
        ));
    });