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