Using virtual foreign keys

Tutorial

"Virtual foreign keys" are a powerful feature that allow you to impose virtual foreign key relationships between data in unrelated tables. Tables can be either in the same database or in different databases, and you don't need to alter the database schema. Virtual foreign keys are a super convenient way to orchestrate complex queries on disparate data without having to hand-code server-side logic.

Once you've set up a virtual foreign key relationship, it's easy to perform CRUD operations that read, write, update, and delete records on the "virtually" related data, all with a single API call that passes the virtual foreign key as an API parameter. Try these examples in the 'API Docs' tab of the DreamFactory Admin Console or from the command line with cURL.

API Endpoint

GET https://{url}/api/v2/{api_name}/_table/{table_name}?related={virtual_foreign_key_field}

API Docs Screenshot

Example - Fetch contact and related customer records in the same database with username as a virtual foreign key.

Say we have two tables in the same database that we need to connect: a 'contact' table with basic contact information and a 'customer' table that lists transactions of items we've sold to customers. These tables don't have a foreign key relationship in our database schema, but the contact table has a field called 'email' and the customer table has a field called 'username', which is an email address.

We can use the customer table's 'username' field as a virtual foreign key to the contact table's 'email' field, allowing us to make a single API call to the contact table to GET a set of contact records and related records in the customer table where 'username' matches 'email'.

To set up the virtual foreign key, navigate to the Schema tab and select field that is going to the virtual foreign key. In this example, we select the 'username' field on the 'contact' table. Then in the 'username' field definition form, check 'Foreign Key', 'Virtual Foreign Key', 'contact' for Reference Table, and 'email' for 'Reference' field.

To see the virtual foreign key relationship from the 'contact' table, go to Schema and select the 'contact' table.

Now we can make API calls to the 'contact' table and fetch contacts as well as related customer records in a single API call.

• Table name: contact
• Related parameter in API call:

  customer_by_username

• Request URL:

  https://foo.com/api/v2/db/_table/contact?related=customer_by_username

Example - Fetch contact, contact_info_by_contact_id, and contact_group_relationship_by_contact_id records in one API call.

• Table name: contact
• Related parameter in API call:

  contact_info_by_contact_id, contact_group_relationship_by_contact_id

• Request URL:

  https://foo.com/api/v2/db/_table/contact?related=contact_info_by_contact_id%2C%20contact_group_relationship_by_contact_id