Manage Subresources

Manage Subresources
lac53
Subresources are nested documents that deliver document-oriented multi-table results to clients, instead of flat relational tables. They are convenient to client code and can reduce latency. The documents deliver your required data in a single server call, with pagination for large results. Subresources can come from other databases, such as a MongoDB database, or other non-SQL sources. They can call other REST-based services in JavaScript subresources.
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 Creator
returns 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:
  1. With your API open, in the Create section, click
    Resources
    .
  2. Select the resource for which you want to define a subresource from the resource list, and then click
    New Level
    .
    The Add Level to <
    containing resource
    > window opens.
  3. Complete the following fields, and then click
    Add Level
    :
    Resource Type
    Defines the type of subresource that you want to create.
    Values:
      • Ⓣ 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 SQL
        Resource:
        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.
      • Ⓙ JavaScript
        Resource:
        (Advanced users) Subresources that you supply the server-side JavaScript that is executed whenever the resource is accessed and returns JSON. You can control resource processing by defining JavaScript subresources.
        For more information about how to define a JavaScript resource type, see Define JavaScript Resource Types.
    Default:
    Table-based Resource - Existing Relationships
    Existing 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 Name
    The name for the subresource.
    Unique:
    Yes
    Join
    Defines 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 Creator
    predefines the join based on the foreign keys, or physical to-parent relationships.
    Examples:
    • 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 a
      virtual
      resource attribute in the containing resource. You can use this syntax to do "fuzzy" joins, for example, where the value for the
      phone_number
      does not exactly match the
      phone
      virtual resource attribute of the containing resource:
      phone_number = [[phone]]
      For more information about viewing an API sample that joins tables that have approximate keys, see Approximate Keys API Sample.
    Is a collection
    Defines this subresource as a parent subresource. Subresources are
    collections
    if 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 most
    Layer7 Live API Creator
    can expect only one row.
    Default:
    Selected
    The subresource is created and the
    Resource
    tab appears.
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 Creator
efficiently optimizes resources that combine data from different databases. For example,
Layer7 Live API Creator
retrieves customers and then retrieves the
OrdersFromSample
rows.
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]
Example:
In the following example,
customer_number
and
region_ident
are columns in the resource, and
custnum
and
regident
are 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
Join
field 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
PurchaseOrder
table from the
Demo
sample API.
Follow these steps:
  1. In API Creator, open the
    Demo
    sample API.
  2. In the Create section, click
    Resources
    .
    The
    Resource
    tab displays.
  3. In the list of resources, select
    PurchaseOrders
    as the containing resource, and then click
    New Level
    .
    The
    Add Level to PurchaseOrders
    window opens displaying related tables. The following image shows this window:
    Demo_Resources_PurchaseOrders.png
    The
    Table-based Resource - Existing Relationships
    resource type is selected by default. The
    Existing Relationship
    list is of related objects based on your schema's foreign keys. Since the
    PurchaseOrders
    resource is a base entity, the list of related tables is based on relationship roles.
  4. For
    Existing Relationship
    , select the
    LineItem
    as the related table from the list, and then click
    Add Level
    .
The
LineItemList
subresource 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
Demo
sample API. This subresource is from another database, to which the
Demo
API is connected.
For more information about how to integrate data, see Integrate Data and Systems.
Follow these steps:
  1. On the
    Resource
    tab, in the list of resources, expand the
    MDBDemoCustomers
    resource. The
    financeOrders
    resource displays. The following shows the
    MDBDemoCustomers
    resource:
    Demo_Resources_MDBDemoCustomers.png
  2. Select the
    financeOrders
    subresource. The following image shows this subresource:
    Screen Shot 2019-03-25 at 10.41.26 AM.png
    The
    financeOrders
    subresource contains the nesting of related orders from another database to which the
    Demo
    API is connected, the
    finance
    data source. This subresource returns
    orders
    from the
    finance
    data source.