What are REST endpoints that JOIN table data?

REST endpoints that join table data typically allow you to retrieve and combine information from multiple tables or data sources in a single API call. These endpoints enable complex queries and data aggregations, often using JOIN operations, to return a unified dataset, simplifying data retrieval and reducing the need for multiple requests and manual data processing. They are commonly used in database systems and data analytics platforms to efficiently access and manipulate interconnected data.

Experienced coders can implement the fundamental CRUD (create, retrieve, update, delete) interactions pretty quickly, particularly when using a capable web framework such as Laravel or Ruby on Rails.

So far, so good. However, when building an API-driven application, the real work often lies beyond these boilerplate features. For instance, how would the API handle a table join? What about a nested update in which both an employee profile and his associated department assignment were changed? How are endpoints responsible for manipulating multiple tables wrapping the queries into a transaction to ensure the queries are treated as an atomic operation?

Table of Contents:

The DreamFactory team has spent a tremendous amount of time and effort solving these sorts of challenges with REST endpoints. In this first of a several part series covering DreamFactory’s advanced database API features, I’ll walk you through several examples demonstrating platform capabilities in this regards.

Joining Related Data in a RESTful Web API

The DreamFactory platform is best known for its ability to auto-generate full-featured APIs with REST endpoints for a wide variety of databases (20 at last count, including MS SQL Server, MySQL, Oracle, and MongoDB – watch this video to learn more). Beyond generating endpoints for interacting with tables, views, and stored procedures, DreamFactory will also scan the database schema for foreign key relationships, creating keyword aliases which can be passed along as parameters to produce a table join.

For example, after generating an API for the official MySQL example database, DreamFactory will recognize six foreign key relationships associated with the employees table:

Note the type column: DreamFactory can recognize the four common types of table relationships, including “belongs to”, “has one”, “has many”, and “many to many”. In the case of the employees table, DreamFactory recognizes two “many to many” and four “has many” relationships.

The name column identifies the auto-generated join alias. You will use these aliases to execute SQL joins via an API call. For instance, we can retrieve all records found in the employees table via this URI:

/api/v2/mysql/_table/employees

This call will return the table record in JSON format. Here is some example output:

{
    "emp_no": 10001,
    "birth_date": "1953-09-02",
    "first_name": "Georgi",
    "last_name": "Facello",
    "gender": "M",
    "hire_date": "1986-06-26"
},
{
    "emp_no": 10002,
    "birth_date": "1964-06-03",
    "first_name": "Bezalel",
    "last_name": "Simmels",
    "gender": "F",
    "hire_date": "1985-11-21"
},

Now suppose we want to additionally retrieve each employee’s assigned department. We can pass the related key with the ‘departments_by_dept_emp` alias to the API call:

/api/v2/mysql/_table/employees?related=departments_by_dept_emp

This will produce the following response:

{
  "emp_no": 10001,
  "birth_date": "1953-09-02",
  "first_name": "Georgi",
  "last_name": "Facello",
  "gender": "M",
  "hire_date": "1986-06-26",
  "departments_by_dept_emp": [
    {
      "dept_no": "d005",
      "dept_name": "Development"
    }
  ]
},
{
  "emp_no": 10002,
  "birth_date": "1964-06-03",
  "first_name": "Bezalel",
  "last_name": "Simmels",
  "gender": "F",
  "hire_date": "1985-11-21",
  "departments_by_dept_emp": [
    {
      "dept_no": "d007",
      "dept_name": "Sales"
    }
  ]
}

Joining Multiple Tables Using a REST API

DreamFactory also supports the ability to join more than two tables together. This is accomplished by adding more than one alias to the related parameter. The following example will join the employee record associated with the primary key 10001 with the departments and salaries tables:

/api/v2/mysql/_table/employees/10001?related=departments_by_dept_emp,salaries_by_emp_no

This will produce the following record:

{
  "emp_no": 10001,
  "birth_date": "1953-09-02",
  "first_name": "Georgi",
  "last_name": "Facello",
  "gender": "M",
  "hire_date": "2019-06-26",
  "departments_by_dept_emp": [
    {
      "dept_no": "d005",
      "dept_name": "Development"
    }
  ],
  "salaries_by_emp_no": [
    {
      "emp_no": 10001,
      "salary": 60117,
      "from_date": "2019-06-26",
      "to_date": "2020-06-26"
    },
    {
      "emp_no": 10001,
      "salary": 65433,
      "from_date": "2020-06-27",
      "to_date": "2021-06-26"
    }
  ]
}

If you want to join all available tables together, you can pass an asterisk to the related parameter:

/api/v2/mysql/_table/employees/10001?related=*

REST endpoints: Overriding the Default Join Alias

You can override the default join aliases to a more preferable string. For instance you might want to instead use departments instead of departments_by_dept_emp. You can update the alias by navigating to DreamFactory’s Schema tab, choosing the API, then choosing the join alias under the Relationships section and making the desired modification. Once done your URI will look like this:

/api/v2/mysql/_table/employees?related=departments

The output will look like this:

{
  "emp_no": 10001,
  "birth_date": "1953-09-02",
  "first_name": "Georgi",
  "last_name": "Facello",
  "gender": "M",
  "hire_date": "1986-06-26",
  "departments": [
    {
      "dept_no": "d005",
      "dept_name": "Development"
    }
  ]
},
{
  "emp_no": 10002,
  "birth_date": "1964-06-02",
  "first_name": "Bezalel",
  "last_name": "Simmel",
  "gender": "F",
  "hire_date": "1985-11-21",
  "departments": [
    {
      "dept_no": "d007",
      "dept_name": "Sales"
    }
  ]
}

Conclusion

As this post demonstrated, DreamFactory can reduce a great deal of tedious work associated with API-driven data interfaces. In the next installment you’ll learn how a DreamFactory-generated API & REST endpoints can perform nested and bulk inserts and updates involving multiple tables.

Related Reading:

Jason Gilmore

Jason is the author of almost a dozen books on web development, including most recently Easy Laravel 5, and Beginning PHP and MySQL, 4th Edition. He’s the co-founder of the CodeMash Conference, one of the largest software conferences in the Midwestern United States.

Jason serves as a technical advisor to the boards of several technology startups. His free time is spent playing with his kids and reading.