Terence Bennett - September 12, 2017One of DreamFactory’s most popular features is the ability to pull in foreign key related records in an API call, and even to create your own virtual relationships between disparate data sets. This make compiling all the data you need in one place a matter of asking the API for the related items. You can use filters and ordering to select the exact records you want to display, and their related items come along for the ride. Simple. Easy.
But what if you want to take that another step further? What if you wanted to use DreamFactory’s database filtering mechanism to filter the data in the related records as well?
Enter Event Scripting to save the day. In this tutorial we’ll walk through writing a post-process event script in V8JS to filter our related records. If you’re not up to speed on DreamFactory and server side scripting, be sure to check out the documentation.
The Setup
We’re going to be using sample data from the Address Book sample apps that DreamFactory provides. If you haven’t already, go ahead and import one of these on the Apps tab. You won’t need to actually go through the whole setup process (unless you plan to use the app later.) We just want the sample data for this project.
This will import the contact data into the service called db. We will use this throughout our examples in this tutorial. If you are using a different set of data, be sure to adjust accordingly.
The Status Quo
To get a sense of what we’re working with, let’s do a basic filtered and related call on this data. Do a GET on db/_table/contact?filter=(first_name LIKE ‘Jon’)&related=contact_info_by_contact_id and look at the results:
{
"resource": [
{
"id": 1,
"first_name": "Jon",
"last_name": "Yang",
"image_url": "",
"twitter": "@jon24",
"skype": "jon24",
"notes": "",
"contact_info_by_contact_id": [
{
"id": 1,
"ordinal": 0,
"contact_id": 1,
"info_type": "home",
"phone": "500 555-0162 ",
"email": "[email protected]",
"address": "3761 N. 14th St",
"city": "MEDINA",
"state": "ND",
"zip": "58467",
"country": "USA"
},
{
"id": 2,
"ordinal": 0,
"contact_id": 1,
"info_type": "work",
"phone": "500 555-0110 ",
"email": "[email protected]",
"address": "2243 W St.",
"city": "MEDINA",
"state": "ND",
"zip": "58467",
"country": "USA"
},
{
"id": 3,
"ordinal": 0,
"contact_id": 1,
"info_type": "mobile",
"phone": "500 555-0184 ",
"email": "[email protected]",
"address": "5844 Linden Land",
"city": "MEDINA",
"state": "ND",
"zip": "58467",
"country": "USA"
}
]
},
{
"id": 61,
"first_name": "Jon",
"last_name": "Zhou",
"image_url": "",
"twitter": "@jon28",
"skype": "jon28",
"notes": "",
"contact_info_by_contact_id": [
{
"id": 181,
"ordinal": 0,
"contact_id": 61,
"info_type": "home",
"phone": "702-555-0144 ",
"email": "[email protected]",
"address": "3531 Brookview Drive",
"city": "AMES",
"state": "IA",
"zip": "50011",
"country": "USA"
},
{
"id": 182,
"ordinal": 0,
"contact_id": 61,
"info_type": "work",
"phone": "735-555-0197 ",
"email": "[email protected]",
"address": "1085 Greenbelt Way",
"city": "AMES",
"state": "IA",
"zip": "50011",
"country": "USA"
},
{
"id": 183,
"ordinal": 0,
"contact_id": 61,
"info_type": "mobile",
"phone": "567-555-0176 ",
"email": "[email protected]",
"address": "6968 Mildred Ln.",
"city": "AMES",
"state": "IA",
"zip": "50011",
"country": "USA"
}
]
}
]
}
This call queries the contact table and returns all the records with a first name of Jon. It also returns all of the records from the contact_info table that are related. This is probably too much information for some applications. So for our related filter, let’s say we only want to get work info.
The Script
We’re going to create a new post-process script that runs on GET db/_table/contact. It will be V8JS, and we want it to be both active and able to modify the response.
Here is the script. Paste the whole thing in and save it, and then we’ll walk through the parts.
var relationship_name = 'contact_info_by_contact_id';
var related_filter_name = 'contact_info_by_contact_id_filter';
var related_service_name = 'db';
var related_table_name = 'contact_info';
var related_id_field = 'id';
var related_endpoint = related_service_name + '/_table/' + related_table_name;
var options = {};
options.parameters = {};
var params = event.request.parameters;
if (params.hasOwnProperty('related')) {
if (params.hasOwnProperty(related_filter_name)) {
if (params.related.hasOwnProperty(relationship_name)) {
options.parameters.fields = related_id_field;
options.parameters.filter = params[related_filter_name];
var related_result = platform.api.get(related_endpoint, null, options);
var data = related_result.content;
if (data.error) throw data.error.message;
if (!data.resource) throw 'No records in response';
var ids = data.resource.map(function(record) {return record[related_id_field];});
event.response.content.resource.forEach(function(parent_record){
parent_record[relationship_name] = parent_record[relationship_name].filter(function(obj){
return (ids.indexOf(obj.id) >= 0);
});
});
}
}
}
Let’s look at the parts now. The first 6 lines establish the variable items we’re going to be working with. We’re going to specify the relationship that we’re working with as well as the service name and table name that contain the related data. We’re going to create a related_filter_name that the script will look for in the parameters, and we’ll construct the endpoint for calling the related items directly.
In lines 7 and 8 we’ll create our options variable for passing parameters and headers into a platform.api call, and then in line 9 we store the event request parameters in a variable called params (this is just to improve readability.)
Now the functionality.
if (params.hasOwnProperty('related')) {
if (params.hasOwnProperty(related_filter_name)) {
if (params.related.hasOwnProperty(relationship_name)) {
This checks to see if you included the related parameter. Then it checks to see if you included the related filter parameter name (contact_info_by_contact_id_filter.) And lastly it checks to see if the related value was the designated value on line 1 (contact_info_by_contact_id.)
options.parameters.fields = related_id_field;
options.parameters.filter = params[related_filter_name];
This sets fields=id and filter=[the value of contact_info_by_contact_id_filter]
var related_result = platform.api.get(related_endpoint, null, options);
This line makes a call to the related table asking for all of its ids. Essentially we just built a call (through variables) to db/_table/contact_info?fiels=id&filter=[the value of contact_info_by_contact_id_filter]
The ids will be stored in the related result variable.
var data = related_result.content;
if (data.error) throw data.error.message;
if (!data.resource) throw 'No records in response';
Now we’re going to store the results from that API call in a new variable and check its validity. First checking to see if it has an error property, and second checking to see if it has a resource array in it (which it should if it has data.)
var ids = data.resource.map(function(record) {return record[related_id_field];});
Now we use the map function to store all the items in a single variable array called ids.
event.response.content.resource.forEach(function(parent_record){
parent_record[relationship_name] = parent_record[relationship_name].filter(function(obj){
return (ids.indexOf(obj.id) >= 0);
});
});
This is where we combine these two data sets. We loop through the original response from DreamFactory and only return the related items whose id matches a value in the ids array.
The Solution
So now we should be able to incorporate our related filter into our original API call and get back filtered results. Do a GET on db/_table/contact?filter=(first_name LIKE ‘Jon’)&related=contact_info_by_contact_id&contact_info_by_contact_id_filter=(info_type like ‘work’)
(notice we included the contact_info_by_contact_id_filter param and asked for only work contact_info types.
You should get this result:
{
"resource": [
{
"id": 1,
"first_name": "Jon",
"last_name": "Yang",
"image_url": "",
"twitter": "@jon24",
"skype": "jon24",
"notes": "",
"contact_info_by_contact_id": [
{
"id": 2,
"ordinal": 0,
"contact_id": 1,
"info_type": "work",
"phone": "500 555-0110 ",
"email": "[email protected]",
"address": "2243 W St.",
"city": "MEDINA",
"state": "ND",
"zip": "58467",
"country": "USA"
}
]
},
{
"id": 61,
"first_name": "Jon",
"last_name": "Zhou",
"image_url": "",
"twitter": "@jon28",
"skype": "jon28",
"notes": "",
"contact_info_by_contact_id": [
{
"id": 182,
"ordinal": 0,
"contact_id": 61,
"info_type": "work",
"phone": "735-555-0197 ",
"email": "[email protected]",
"address": "1085 Greenbelt Way",
"city": "AMES",
"state": "IA",
"zip": "50011",
"country": "USA"
}
]
}
]
}
Congratulations! You’re now ready to filter the related records in your data. You should be able to modify this and use it as you see fit in your specific context.
This script will be posted in the example-scripts repository on GitHub. Be sure to check out the repo and contribute a script of your own!
Terence Bennett, CEO of DreamFactory, has a wealth of experience in government IT systems and Google Cloud. His impressive background includes being a former U.S. Navy Intelligence Officer and a former member of Google’s Red Team. Prior to becoming CEO, he served as COO at DreamFactory Software.