You can also define subresources for related parent-child data. For example, you might define a customer that includes its purchase orders and line items.
Layer7 Live API Creatorreturns JSON results as a nested document model with nesting for subresources. This document model reduces latency and simplifies applications.
The Data Source Attributes list displays a list of the columns from the table that originates from the data source.
In this article:
Create a Subresource
Prerequisite:You have created the containing resource as a table-based resource.
Follow these steps:
- With your API open, in the Create section, clickResources.
- Select the resource for which you want to define a subresource from the resource list, and then clickNew Level.The Add Level to <containing resource> window opens.
- Complete the following fields, and then clickAdd Level:Resource TypeDefines the type of subresource that you want to create.Values:
Default:ⓉTable-based Resource - Existing RelationshipsExisting Relationship (Table-based Resource - Existing Relationships)Defines the relationship that you want to use for your table-based resource.For more information about relationships, see Manage Relationships.Entity (Table-Based Resource - Manual Join)Defines the entity to which you want to define a manual join from your subresource.Resource NameThe name for the subresource.Unique:YesJoinDefines how you want to join this subresource with the containing resource (the join condition). Enter the resource attributes of the containing resource within brackets (). The attributes that you define within the brackets must follow the equals (=) symbol. For the Table-based Resource - Existing Relationships resource type,Layer7 Live API Creatorpredefines the join based on the foreign keys, or physical to-parent relationships.Examples:
- Ⓣ Table-based Resource - Existing Relationships:Subresources that are linked to the containing table-based resource. The relationship can be based on foreign keys or virtual relationships that are defined in API Creator.For more information about how to define a table-based resource type, see Define Table-Based Resource Types.
- Ⓣ Table-based Resource - Manual Join:Subresources that are linked to existing base SQL entities (tables or views).For more information about how to define a table-based resource type, see Define Table-Based Resource Types.
- Ⓢ Free SQLResource:Subresources that you specify the SQL to execute.For more information about how to define a Free SQL resource type, see Define Free SQL Resource Types.
Is a collectionDefines this subresource as a parent subresource. Subresources arecollectionsif the containing resource can include many rows for a single row. They can be collections only if the tables are parent tables of the containing resource's table.Best practice:Define top-level resources as collections. At mostLayer7 Live API Creatorcan expect only one row.The subresource is created and theDefault:SelectedResourcetab appears.
- To retrieve child rows for a containing resource, enter the following notation:fk = [pk]
- To retrieve the parent for a containing child, enter the following notation:pk = [fk]Joins are applicable only to the resource in which you define it. They are not virtual relationships.Use the double square bracket syntax to refer to avirtualresource attribute in the containing resource. You can use this syntax to do "fuzzy" joins, for example, where the value for the
does not exactly match thephone_number
virtual resource attribute of the containing resource:phonephone_number = [[phone]]For more information about viewing an API sample that joins tables that have approximate keys, see Approximate Keys API Sample.
The subresource is saved to the containing resource. The subresource is displayed indented under the resource level.
Join Resources with Containing Resources
You can define resources that combine data from different databases (join resources with containing resources) by adding join conditions to your subresource. The join condition defines how the resource is joined with the containing resource.
Layer7 Live API Creatorefficiently optimizes resources that combine data from different databases. For example,
Layer7 Live API Creatorretrieves customers and then retrieves the
Reference Entity Columns in Containing Resources
If you have joined the resource with a containing resource, you can reference entity columns in the containing resource using the following syntax:
col_1_resource = [col_1_containing_resource] and col_2_resource = [col_2_containing_resource]
In the following example,
region_identare columns in the resource, and
regidentare columns in the containing resource:
customer_number = [custnum] and region_ident = [regident]
You can use this join syntax for resources that are defined when you are connected to a data source that uses a data source provider.
If a foreign key that fits the definition of the resource exists in the database, the
Joinfield included this information. You can change this value.
Example: Create a Subresource with Related Tables
This example shows how to create a resource using the
PurchaseOrdertable from the
Follow these steps:
- In API Creator, open theDemosample API.
- In the Create section, clickResources.TheResourcetab displays.
- In the list of resources, selectPurchaseOrdersas the containing resource, and then clickNew Level.TheAdd Level to PurchaseOrderswindow opens displaying related tables. The following image shows this window:TheTable-based Resource - Existing Relationshipsresource type is selected by default. TheExisting Relationshiplist is of related objects based on your schema's foreign keys. Since thePurchaseOrdersresource is a base entity, the list of related tables is based on relationship roles.
- ForExisting Relationship, select theLineItemas the related table from the list, and then clickAdd Level.
LineItemListsubresource is created.
Example: Create a Subresource with Related Tables from Another Database
You can create a subresource with related tables from another database. The following example shows an example subresource in the
Demosample API. This subresource is from another database, to which the
DemoAPI is connected.
For more information about how to integrate data, see Integrate Data and Systems.
Follow these steps:
- On theResourcetab, in the list of resources, expand theMDBDemoCustomersresource. ThefinanceOrdersresource displays. The following shows theMDBDemoCustomersresource:
- Select thefinanceOrderssubresource. The following image shows this subresource:ThefinanceOrderssubresource contains the nesting of related orders from another database to which theDemoAPI is connected, thefinancedata source. This subresource returnsordersfrom thefinancedata source.