Version 25.3.9411

• Relationships
  • Using the Schema Editor
    • One-to-Many Relationships
    • Many-to-One Relationships
    • Partner Attribute
• OData Expand Syntax
  • One-to-Many Relationship
  • Many-to-One Relationship
  • Multiple Expansions
• Expand Options
• Select
• Filter

API Server supports OData $expand functionality, which allows a single request to provide data from a record and from related entities. To enable this, you must define the relationships between entities in your API Server tables.

Relationships

Relationships between resources must be defined on the API Server API page before you can use the OData $expand functionality. See API Page for details. You have several ways to define relationships between entities. The following examples use Product/Category relationships.

Using the Schema Editor

You can manually define relationships in the schema editor by adding the relationships XML attribute to the column defining the relationship. The attribute should follow this format: relationshipName(TableName.ColumnName). Separate multiple relationships with a pipe symbol (|). The relationship definition formatting depends on its cardinality. Your cardinality options are described in the following sections.

One-to-Many Relationships

Define one-to-many relationships in the schema entry corresponding to the primary key in the schema of the parent object. Relationship names start with a asterisk (*) to indicate cardinality, as shown in the following example:

<attr name="CategoryID" key="true" type="int" columnsize="11" 
      isNullable="false" readonly="false" relationships="*Products(Product.CategoryID)" 
      desc="" />

Many-to-One Relationships

Define many-to-one relationships in the line corresponding to the foreign key in the schema of the child object:

<attr name="CategoryID" key="false" type="int" columnsize="11" 
      isNullable="true" readonly="false" relationships="ProductsCategory(Category.CategoryID)" 
      desc="" />

Partner Attribute

You can add an @ prefix to the name of a relationship to add the Partner attribute, as shown in the examples below.

Tip: See the OData documentation on the Partner attribute for more information.

For a one-to-many relationship that includes an asterisk (*):

relationships="*@Products(Product.CategoryID)"

For a many-to-one relationship:

relationships="@ProductsCategory(Category.CategoryID)"

Important: The application does not perform any validation on the Partner attribute. When using this feature, you must ensure that the following conditions are met:

• The reverse relationship is defined and marked with the Partner attribute.
• The name of the reverse relationship is exactly the same as the name of the current item, including whether it includes or excludes plural.

OData Expand Syntax

Once you’ve defined the relationships between related entities, the $expand keyword enables the selection of record properties as well as the set of related objects inline in a single request. Refer to the OData documentation for more details on the syntax of $expand queries.

One-to-Many Relationship

For each base record, retrieve all related objects.

Request

http://MyServer:MyPort/api.rsc/Categories(1)?$expand=Products

Response

{
    "@odata.context": "http://localhost:8153/api.rsc/$metadata#Categories",
    "value": [
        {
            "CategoryID": "1",
            "CategoryName": "Books",
            "Products": [
                {
                    "ProductID": "101",
                    "Price": "45",
                    "Name": "Astronomy 101",
                    "Supplierid": "1",
                    "CompanyId": "1",
                    "CategoryId": "1"
                },
                {
                    "ProductID": "102",
                    "Price": "50",
                    "Name": "Physics 101",
                    "Supplierid": "2",
                    "CompanyId": "1",
                    "CategoryId": "1"
                },
                {
                    "ProductID": "103",
                    "Price": "65",
                    "Name": "Chemistry 101",
                    "Supplierid": "3",
                    "CompanyId": "1",
                    "CategoryId": "1"
                }
            ]
        }
    ]
}

Many-to-One Relationship

For each record, retrieve the related parent record.

Request

http://MyServer:MyPort/api.rsc/Products?$expand=ProductsCategory&$filter=price lt 60&$orderby=price desc

Response

{
    "@odata.context": "http://localhost:8153/api.rsc/$metadata#Products",
    "value": [
        {
            "ProductId": 102,
            "Name": "Physics 101",
            "Price": "50",
            "CategoryId": "1",
            "ProductsCategory": [
                {
                    "CategoryName": "Books",
                    "CategoryId": "1"
                }
            ]
        },
        {
            "ProductId": 101,
            "Name": "Astronomy 101",
            "Price": "45",
            "CategoryId": "1",
            "ProductsCategory": [
                {
                    "CategoryName": "Books",
                    "CategoryId": "1"
                }
            ]
        }
    ]
}

Multiple Expansions

The $expand parameter can take a comma-separated list of values, combining many-to-one and one-to-many relationships.

Request

http://MyServer:MyPort/api.rsc/Products(102)?$expand=Supplier,ProductReviews

Response

{
    "@odata.context": "http://localhost:8153/api.rsc/$metadata#Products",
    "value": [
        {
            "ProductId": 102,
            "Name": "Physics 101",
            "Price": "50",
            "ProductReviews": [
                {
                    "ProductReviewID": "1001",
                    "ProductId": "102",
                    "Rating": "4.5",
                    "Description": "Easy to read, good illustrations."
                },
                    {
                    "ProductReviewID": "1002",
                    "ProductId": "102",
                    "Rating": "1.5",
                    "Description": "Boring!"
                }
            ],
            "Supplier": [
                {
                    "SupplierId": "1",
                    "SupplierName": "New Science Publishing"
                }
            ]
        }
    ]
}

Expand Options

OData API calls can use $expand options for a more granular definitions of the related entities to return. Expand options are specified as a semicolon-separated list, in parentheses appended to the related entity.

Select

$select defines which columns of the related entity are returned. If not specified, all available values are returned. For example:

http://MyServer:MyPort/api.rsc/Products(102)?$expand=ProductReviews($select=Description),Supplier($select=SupplierName)

Filter

Use $filter to define a criteria to apply on related results. Related entities which do not conform to the criteria are ommitted from the response. For example:

http://MyServer:MyPort/api.rsc/Products(102)?$expand=ProductReviews($select=Rating;$filter=Rating ge 4)