Filtering Related Columns within DreamFactory REST API Queries

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