Expanding Related Data

A relationship declared in entity.json makes the related row available through $expand. The relationship is executed as a SQL JOIN on every supported provider, with the endpoint's authentication, environment rules and column allowlists.

When it applies ​

Portway processes $expand on SQL Table and View endpoints only:

Declaring a relationship ​

Each Relationships entry names a navigation and a target SQL endpoint; the target's schema, table and AllowedColumns apply:

{
  "DatabaseObjectName": "Items",
  "DatabaseSchema": "dbo",
  "DatabaseObjectType": "Table",
  "PrimaryKey": "ItemCode",
  "AllowedColumns": [
    "ItemCode;ProductNumber",
    "Description;Description",
    "Assortment;AssortmentID"
  ],
  "AllowedMethods": ["GET"],
  "Relationships": [
    {
      "Name": "Category",
      "Target": "Assortments",
      "LocalColumn": "Assortment",
      "TargetColumn": "AssortmentID",
      "Multiplicity": "ToOne"
    }
  ]
}

Fields are validated as identifiers at load. An unresolved Target is logged as a configuration error and the expand returns 400.

Making a request ​

GET /api/prod/Products?$expand=Category&$filter=AssortmentID eq 10

The Category navigation joins Assortments on Assortment = AssortmentID; the related row is nested under Category:

{
  "ProductNumber": "A-100",
  "Description": "Widget",
  "AssortmentID": 10,
  "Category": {
    "AssortmentID": 10,
    "Name": "Tools"
  }
}

Only the target's AllowedColumns are returned. Filters, selection, ordering and paging apply to the base entity, including filters on the foreign key.

Join behavior ​

SELECT TargetColumn, COUNT(*)
FROM YourTargetTable
GROUP BY TargetColumn
HAVING COUNT(*) > 1;

To-one navigations use an INNER JOIN; base rows without a match are omitted. The $count value counts base rows for the $filter without the join.

Limits ​

Related topics ​

• OData Syntax
• HTTP Methods
• Entity Configuration
• SQL Endpoints