Manage Subresources

Manage Subresources
lac42
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 results can 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 create subresources for related parent-child data. For example, you might define a customer that includes its purchase orders and line items. The data is included in the JSON that is returned from issued REST requests. 
CA Live API Creator
 returns JSON results as a nested document model with nesting for subresources. This model reduces latency and simplifies applications.
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 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.
    • Ⓣ Table-based Resource - Manual Join.
       Subresources that are linked to existing base SQL entities (tables or views). 
    • Ⓢ 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.
    • Ⓜ MongoDB
       Resource
      . Subresources that you can connect to a specific MongoDB server, database, and collection.
      For more information about how to define a MongoDB resource type, see Define MongoDB Resource Types.
    Default:
     Table-based Resource - Existing Relationships
    Existing Relationship (Table-based Resource - Existing Relationships)
    Defines the database relationship that you want to use for your table-based resource.
    For more information about database 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. Enter containing resource attributes within brackets ([]). The attributes you define within the brackets must follow the equals (=) symbol. For Table-based Resource - Existing Relationships, the join is predefined based on the foreign keys. 
    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]
      The manual join that is defined for the subresource is only applicable to the resource. This is not a virtual relationship defined as Relationships to Children in the Schema.
    Is a collection 
    This checkbox is typically used only for parent subresources. Subresources are 
    collections
     if there can be many rows for a single row in the containing resource. You can define a subresource as a collection only if the table is a parent table of the containing resource's table.
    Best practice:
     Define top-level resources as collections. At most
    CA Live API Creator
    can expect only one row. 
    For more information, see the "Parent Subresources" section.
    Default:
     Selected
    The subresource is created. The Resource tab appears.
  4. If you want to combine and include the attributes of this subresource in the containing root resource, select the
    Combined
    checkbox, and then save your changes. For more information, see the "Create a Combined Resource" section.
The subresource is saved to the containing resource. The subresource is displayed indented under the resource level.
Create a Combined Resource
Combined resources are SQL table-based subresources whose attributes 
CA Live API Creator
 combines and includes in a containing resource. 
CA Live API Creator
 merges the attributes into the JSON of a containing resource. Combined resources must contain only combined subresources and only attributes with names that do not conflict with the attributes of the containing resource, or of any other subresource that is combined into the same containing resource. Combined resources cannot be a collection.
The attributes from combined resources are read-only; you can send them back as part of a POST or PUT, but
CA Live API Creator
ignores their values.
Prerequisite:
 The subresource that you want to define as combined exists.
Follow these steps:
  1. With your API open, in the Create section, click 
    Resources
    .
    The Resources page appears.
  2. Click the subresource you want to define as combined, select the 
    Combined
     checkbox, and then save your changes.
The subresource is defined as a combined resource.
B2B Sample: Create a Combined Resource
The
B2B Northwind
API includes the 
Items
 resource and a 
Product
 subresource as a combined resource, with a
product_name
 attribute. For each order item, this resource shows the product name.
You can do one of the following:
  • Define a complex object by creating the following resources:
    • A top-level 
      Items
       resource.
    • Product
       subresource .
  • Create a 
    Product
     combined resource with 
    ProductID
     and 
    ProductName
     attributes.
For more information about the B2B sample, see B2B API Sample.
Example: Create a Subresource with Related Tables
The example shown in this procedure creates a custom resource using the
PurchaseOrder
table from the Demo API.
  1. In the Demo API, select 
    PurchaseOrders
     as the containing resource and then click 
    New Level
    . The Add Level to PurchaseOrders window opens displaying related tables. If the level you select is a base entity, suggestions are provided based on relationship roles.
    Screen Shot 2017-08-17 at 11.54.28 AM.png
  2. The resource type 
    Table-based Resource - Existing Relationships 
    is selected by default. A list of related objects based on your schema's foreign keys is presented.
  3. Select a related table 
    LineItem
     from the list, and then click 
    Add Level
The subresource is created. 
Example: Create a Subresource with Related Tables from Another Database
  1. Select the 
    MDBDemoCustomers
     resource. 
  2. Select the 
    financeOrders
     subresource, which is returning orders from a different database. 
    For more information about how your API can integrate multiple databases, see Database Connectivity.
    The following image shows the 
    MDBDemoCustomers
     resource and the nesting of related orders from another database in the Demo API:
    DemoResourceMultiDb.png
Example: Create a Parent Subresource
In the
Demo
API sample, the 
Customers
 resource has 
Product
 as a subresource of Lineitem. Even though 
Product
 is a parent (one side of a one-to-many relationship), it is defined as a subresource.
The following code snippet shows example JSON:
"OrderNumber": 1021,
"TotalAmount": 2272,
"Paid": false,
"CustomerName": "Alpha and Sons",
"salesrep_id": null,
"@metadata": {
"checksum": "A:d65e930bd09632b0"
},
"LineItems": [
  {
    "lineitem_id": 1412,
    "ProductNum": 1,
    "OrderNum": 1021,
    "Quantity": 1,
    "Price": 10,
    "Amount": 10,
    "@metadata": {
      "checksum": "A:88a93cdbb3e64b03"
  },
    "Product": {
    "product_number": 1,
    "name": "Hammer",
    "price": 10,
    "@metadata": {
      "checksum": "A:6569e0d68665a0bc"
    }
  }
},
Creating a parent as a subresource is a common pattern in defining resources over a junction table. 
CA Live API Creator
 collects the parent subresources and computes the join that is sent to the database management system. The automated join processing extends to multiple and nested parents. Parent subresources are the preferred method for retrieving parent data. You can obtain parent data for retrieval without introducing business logic, such as formulas.
 For more information:
View and Process Data from Combined Resources
The following code snippet illustrates the expected JSON result when you define a combined resource, where the combined metadata section lists the attributes originating from the combined resource:
...
  "Order_DetailsList": [
    {
      "OrderID": 2000,
      "ProductID": 7,
      "UnitPrice": 30,
      "Quantity": 2,
      "isHealthy": true,
      "Amount": 60,
      "Discount": 0,
      "ProductName": "Uncle Bob's Organic Dried Pears"
      "@metadata": {
        "href": "http://localhost:8080/rest/default/b2bderbynw/v1/OrderSummary.Order_DetailsList/2000~7",
        "checksum": "A:bf1bf24e035d8830",
        "combined": [
          "ProductName"
        ]
    },
...
The following code snippet illustrates the expected JSON result when you do not define a combined resource:
...
  "Order_DetailsList": [
    {
      "OrderID": 2000,
      "ProductID": 7,
      "UnitPrice": 30,
      "Quantity": 2,
      "isHealthy": true,
      "Amount": 60,
      "Discount": 0,
      "@metadata": {
        "href": "http://localhost:8080/rest/default/b2bderbynw/v1/OrderSummary.Order_DetailsList/2000~7",
        "checksum": "A:bf1bf24e035d8830"
    },
    "Product": {
      "ProductID": 7,
      "ProductName": "Uncle Bob's Organic Dried Pears",
      "@metadata": {
        "href": "http://localhost:8080/rest/default/b2bderbynw/v1/OrderSummary.Order_DetailsList.Product/7",
        "checksum": "A:b1ad26b6d1c32358"
    }
  }
...