Executing SQL Joins in REST APIs Using DreamFactory
by Jason Gilmore • September 16, 2020
As a follow up to our previous blog post about retrieving related data from that REST API, I wanted to dive deeper into the power of the "related records" feature of DreamFactory's database-backed APIs
One of the powerful features of SQL databases is the ability to relate separate data records and perform queries that retrieve that related data all at once. One problem is that this requires a fairly in-depth knowledge of your database, it's schema, and SQL syntax. Another is that this ability of your database is not easily accessed from the web. For the veteran DB and server-side engineers, this may not seem like a big deal, but for a lot of front-end developers, that either don't know or don't want to know all that is involved, it is no small feat.
DreamFactory's SQL DB Service makes getting to your vital SQL data and using it within your applications easier than ever.
Getting the Entire Picture
When using data in your application, you typically want to retrieve, interact, and display related data as complete units, not individual pieces. You have a couple of choices for getting and managing this data from the database.
- You can pull copies of the data from each table in separate queries, keep schema-specific logic in the application to "link" them locally for use or display, and maintain separate copies for updating the database using separate posts for each table.
- You can pull the related data in a single query using possibly complicated SQL join, temporary tables, etc., also keeping logic in your application as to where the data came from and how to update the individual pieces back on the database.
- You can add views (if they are supported by your database) to pull the related data from various tables and return it all together, also adding separate "updating" views for updating portions of that related data back to the database.
Well, anyone that has done it the first way, will quickly tell you what a major pain that is, not to mention the bloat that happens to your application to accomplish it. Separate queries and posts increase network traffic and slow performance. Keeping separate copies of the data obviously requires more memory. All bad news for client or mobile apps.
The second approach is doable if your application uses data as read only. The minute you have to support updating that data back to the database it gets almost as bad as the first.
Views are definitely the way to go if you can get them added to your database and have access to them. DreamFactory supports views just like regular tables (currently only in read only with no schema admin support). Here again, updating the data back to the database is painful as update-able views, if supported, are restrictive and sometimes difficult to deal with.
So what's the solution? As you read in our earlier post about retrieving related records, the DreamFactory REST API can return related records as part of the primary record so that all of the related data is in one JSON record.
DreamFactory's relational database APIs can discover these relationships automatically from the schema in your database. You don't have to provision anything except the connection parameters. Consider the following Contact schema:
GET /api/v2/database/_table/?names=Contacts,ContactInfos,ContactGroups,ContactRelationships
{
"table": [
{
"name": "Contacts",
"primary_key": "contactId",
"field": [
{
"name": "contactId",
"type": "id",
"db_type": "int(11)",
"allow_null": false,
"auto_increment": true,
"is_primary_key": true
},
...
],
"related": [
{
"name": "ContactInfos_by_contactId",
"type": "has_many",
"ref_table": "ContactInfo",
"ref_field": "contactId",
"field": "contactId"
},
{
"name": "ContactRelationshipss_by_contactId",
"type": "has_many",
"ref_table": "ContactRelationships",
"ref_field": "contactId",
"field": "contactId"
},
{
"name": "ContactGroupss_by_ContactRelationships",
"type": "many_many",
"ref_table": "ContactGroups",
"ref_field": "contactGroupId",
"join": "ContactRelationships(contactId,contactGroupId)",
"field": "contactId"
}
]
},
{
"name": "ContactInfo",
"primary_key": "infoId",
"field": [
{
"name": "infoId",
"type": "id",
"db_type": "int(11)",
"allow_null": false,
"auto_increment": true,
"is_primary_key": true
},
{
"name": "contactId",
"type": "reference",
"db_type": "int(11)",
"allow_null": true,
"is_foreign_key": true,
"ref_table": "Contacts",
"ref_fields": "contactId"
},
...
],
"related": [
{
"name": "Contacts_by_contactId",
"type": "belongs_to",
"ref_table": "Contacts",
"ref_field": "contactId",
"field": "contactId"
}
]
},
{
"name": "ContactGroups",
"primary_key": "contactGroupId",
"field": [
{
"name": "contactGroupId",
"type": "id",
"db_type": "int(11)",
"allow_null": false,
"auto_increment": true,
"is_primary_key": true
},
...
],
"related": [
{
"name": "ContactRelationshipss_by_contactGroupId",
"type": "has_many",
"ref_table": "ContactRelationships",
"ref_field": "contactGroupId",
"field": "contactGroupId"
},
{
"name": "Contactss_by_ContactRelationships",
"type": "many_many",
"ref_table": "Contacts",
"ref_field": "contactId",
"join": "ContactRelationships(contactGroupId,contactId)",
"field": "contactGroupId"
}
]
},
{
"name": "ContactRelationships",
"primary_key": "contactRelationshipId",
"field": [
{
"name": "contactRelationshipId",
"type": "id",
"db_type": "int(11)",
"allow_null": false,
"auto_increment": true,
"is_primary_key": true
},
{
"name": "contactId",
"type": "reference",
"db_type": "int(11)",
"allow_null": true,
"is_foreign_key": true,
"ref_table": "Contacts",
"ref_fields": "contactId"
},
{
"name": "contactGroupId",
"type": "reference",
"db_type": "int(11)",
"allow_null": true,
"is_foreign_key": true,
"ref_table": "ContactGroups",
"ref_fields": "contactGroupId"
}
],
"related": [
{
"name": "ContactGroups_by_contactGroupId",
"type": "belongs_to",
"ref_table": "ContactGroups",
"ref_field": "contactGroupId",
"field": "contactGroupId"
},
{
"name": "Contacts_by_contactId",
"type": "belongs_to",
"ref_table": "Contacts",
"ref_field": "contactId",
"field": "contactId"
}
]
}
]
}
The schema states:
- Contacts have directly related records called ContactInfos.
- Contacts also have indirect relationship with ContactGroups, via ContactRelationships junction table.
- Contacts can have many ContactInfos and belong to multiple ContactGroups.
- Each ContactInfo can only apply to one Contact, linked by contactId field.
- Each ContactGroup "contains" multiple Contacts, linked by rows in ContactRelationships table.
When retrieving a record by id or using the filter string request along with the "related" URL parameter, DreamFactory will pull the existing relationships, and add them to the requested record(s) as arrays using the relationship name as the field name, (you can also pass '*' as the value of "related" parameter to get all relationships). For example, retrieving the record for a specific contact with primary key 12:
GET /api/v2/database/_table/contacts/12?related=ContactInfos_by_contactId,ContactRelationships_by_contactId,ContactGroups_by_ContactRelationships
{
"contactId": 12,
"firstName": "Curtis",
"lastName": "Lu",
"imageUrl": "",
"twitter": "@curtis9",
"skype": "curtis9",
"ContactInfos_by_contactId": [
{
"infoId": 34,
"contactId": 12,
"infoType": "Home",
"phone": "500 555-0137",
"email": "curtis9@Home.com",
"address": "5927 Rainbow Dr",
"city": "UPPER TRACT",
"state": "WV",
"zip": "26866",
"country": "USA"
},
{
"infoId": 35,
"contactId": 12,
"infoType": "Work",
"phone": "500 555-0136",
"email": "curtis9@Work.com",
"address": "5167 Condor Place",
"city": "UPPER TRACT",
"state": "WV",
"zip": "26866",
"country": "USA"
},
{
"infoId": 36,
"contactId": 12,
"infoType": "Mobile",
"phone": "500 555-0177",
"email": "curtis9@Mobile.com",
"address": "1873 Mt. Whitney Dr",
"city": "UPPER TRACT",
"state": "WV",
"zip": "26866",
"country": "USA"
}
],
"ContactRelationshipss_by_contactId": [
{
"contactRelationshipId": 12,
"contactId": 12,
"contactGroupId": 3
}
],
"ContactGroupss_by_ContactRelationships": [
{
"contactGroupId": 3,
"groupName": "South East"
}
]
}
Creating Records and Relationships
When you create new records using our REST API via the POST command, you can add new relationships to the records in two ways:
- Create new related records and automatically create the relationship between them
- Create a relationship to other already existing records (updating them at the same time).
For this example, let's add the always popular Joe Smith who works for ACME Inc. to our Contacts. At the same time we will add some work information and create a new group "ACME Inc." for his company. We also want to add Joe to our existing "Sales" contact group:
POST /api/v2/database/_table/contacts
{
"firstName": "Joe",
"lastName": "Smith",
"twitter": "",
"skype": "acme_joe",
"ContactInfos_by_contactId": [
{
"infoType": "Work",
"phone": "555-555-1234",
"email": "joesmith@acme.com",
"address": "1234 Demo Way",
"city": "ATLANTA",
"state": "GA",
"zip": "30303",
"country": "USA"
}
],
"ContactGroups_by_ContactRelationships": [
{
"groupName": "ACME Inc."
},
{
"contactGroupId": 1,
"groupName": "Sales"
}
]
}
Notice that no primary key is included in the related ContactInfo or the new ContactGroup records. This indicates that they are new records that need to be created. After the records are created, the relationships are automatically created. For the ContactInfo, the contactId field is simply set to the newly created Contact records primary key. For the ContactGroups, an entry is added to the ContactRelationships junction table linking the two new records.
To relate the new Contact record with existing records, just include the identifying fields or the whole existing record. Joe is added to the "Sales" group by automatically adding an entry in the ContactRelationships junction table. The "groupName" is not necessary in this case, only the primary key of the group record. In fact, if it is included, it is considered for update on that record (see updates section below). Note that for direct relationships, if the relating field (for ContactInfo it is 'contactId') is in the record, the value will be overwritten with the current Contact record's primary key.
Updating Records Using Patch
When you update existing records using the REST API PATCH command, you can change relationships to the records in three ways:
- Create new related records and automatically create the relationship between them
- Create a relationship to other already existing records (updating them at the same time)
- Remove a relationship to existing related records.
For this example, let's say you and Joe (given the id "33" from earlier creation) become good golfing buddies and he gives you his Twitter handle and home phone and address. Joe also got promoted to a new office across the street, but still with ACME. So you want to add him to your Golf group, update his work info, add his home info, and update his record with the Twitter handle:
PATCH /rest/db/Contacts/33
{
"twitter": "@popular_joe",
"ContactInfos_by_contactId": [
{
"infoType": "Home",
"phone": "555-555-5678",
"email": "golfin_joe@gmail.com",
"address": "999 Back Nine Drive",
"city": "ATLANTA",
"state": "GA",
"zip": "30301",
"country": "USA"
},
{
"infoId": 44,
"contactId": 33,
"infoType": "Work",
"address": "1111 Demo Way"
}
],
"ContactGroups_by_ContactRelationships": [
{
"contactGroupId": 9,
"groupName": "Golf"
}
]
}
As with any of the above scenarios, when updating related records, you can pass only what changed or the whole record, whatever is most convenient for your application. You can also pass Joe's whole record including the primary key to the /rest/db/Contacts. This also works for updating multiple Contact records at once.
As you can imagine, old Joe eventually pulled the ripcord on his golden parachute and left ACME. So now you want to remove him from the Sales and ACME groups, and remove his work contact info, and update his record to remove his Skype work id.
To remove relationships from a record, we include the relating fields to indicate to the service to remove the relationship. With directly related records, just set the relating field to null. In our example, this is "contactId" for ContactInfo records. We include the following URL parameter to help with cleanup of disassociated or abandoned records.
- allow_related_delete - Defaults to false. When the relating field's database schema is set so that it will not allow null values (allow_null is false), i.e. requires a parent value, this record can not stand alone unrelated and thus must be deleted. As a safety catch, this scenario will return an error unless this URL parameter is set to true, at which time the service will attempt to delete the related child record.
For records related via a junction table, add the serviced table's primary key field and set it to null (i.e. the table being serviced here is Contacts, note the "table-dot-field" notation separating it from the rest of the fields in the record). In our example, this is "Contacts.contactId" in the ContactGroups records. The relationship is automatically removed by deleting the linking entry from junction table, in this case ContactRelationships. For records related via junction tables, like ContactGroups, this scenario can be managed by schema setup (on_update/on_delete properties with settings of SET_NULL or CASCADE).
PATCH /api/v2/database/_table/contacts/33
{
"skype": "",
"ContactInfos_by_contactId": [
{
"infoId": 44,
"contactId": null,
"infoType": "Work"
}
],
"ContactGroups_by_ContactRelationships": [
{
"contactGroupId": 3,
"Contacts.contactId": null
"groupName": "ACME"
},
{
"contactGroupId": 1,
"Contacts.contactId": null
"groupName": "Sales"
}
]
}
As before, the "infoType" and "groupName" fields are not required, and are just there for clarification
In Summary
There are several important points to keep in mind when working with related records:
- Native record fields are updated as usual.
- If related records are contained in the request, the fields contained in the request trigger different actions.
- If the related table's primary key field is not included in the related record, it is assumed that this record doesn't exist yet and, if allowed, the related record is created along with the new relationship.
- For Parent-Child Relationships: Existing related records are updated, if allowed. If parent lookup field is in the record and not set to null, the value will be overwritten with the current parent record's primary key (useful when "adopting" other records). If parent lookup field is in the record and it is set to null, indicating that this child needs to be removed from the relationship, the lookup field is set to null, or if allowed (see above) is deleted.
- For Many-to-Many Relationships: If primary key of related table (using dot notation) is set and has a null value, the junction table record is deleted thus removing the relationship.
- All of this logic works for creating or updating single records or multiple records at a time. The "related" parameter can also be used (along with "fields" parameter) when making requests (POST, PATCH, DELETE) and retrieving changed data in one shot (see the API/SDK in the admin panel).
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.
