Jason Gilmore - September 5, 2018

Consider a query which joins employee records found in an employees table with information about their assigned department, the latter of which resides in a table named departments. The relationship is formalized using a key named emp_no. When DreamFactory parses the schema it will create aliases for each relationship, including one for the above-described named something like dept_emp_by_emp_no. The join query will therefore look like this:

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

This would yield a JSON response containing records that look like this:

{
  "emp_no": 10001,
  "birth_date": "1953-09-02",
  "first_name": "Georgi",
  "last_name": "Facello",
  "gender": "M",
  "hire_date": "1986-06-26",
  "birth_year": "1953",
  "dept_emp_by_emp_no": [
    {
      "emp_no": 10001,
      "dept_no": "d005",
      "from_date": "1986-06-26",
      "to_date": "9999-01-01"
    }
  ]
},

If you wanted to limit the related fields to just dept_no and from_date, you would add dept_emp_by_emp_no.fields to the parameter list:

/api/v2/mysql/_table/employees?related=dept_emp_by_emp_no&dept_emp_by_emp_no.fields=dept_no,from_date

This query would yield records with the following structure:

{
  "emp_no": 10001,
  "birth_date": "1953-09-02",
  "first_name": "Georgi",
  "last_name": "Facello",
  "gender": "M",
  "hire_date": "1986-06-26",
  "birth_year": "1953",
  "dept_emp_by_emp_no": [
    {
      "dept_no": "d005",
      "from_date": "1986-06-26"
    }
  ]
},

You can learn more about working with related data inside DreamFactory on our wiki: https://wiki.dreamfactory.com/DreamFactory/Features/Database/Related_Data#Getting_the_Related_Data.

Related reading: Building the Reusable REST API Platform Strategy