OData Syntax

OData gives your SQL endpoints a query language without you writing a line of SQL: clients express what they need in the URL, and Portway translates it safely. This reference covers each supported query option and its syntax.

Query options overview ​

Basic query structure ​

GET /api/{environment}/{endpoint}?{query_options}

Example:

GET /api/prod/Products?$select=ItemCode,Description&$filter=Price gt 50&$orderby=Price desc&$top=10

$select - field selection ​

Select specific fields from the entity:

Syntax ​

$select=field1,field2,field3

Examples ​

# Single field
GET /api/prod/Products?$select=ItemCode

# Multiple fields
GET /api/prod/Products?$select=ItemCode,Description,Price

# All allowed fields (based on entity configuration)
GET /api/prod/Products

Field selection rules ​

• Field names are case-sensitive
• Only fields listed in AllowedColumns can be selected
• Invalid field names return an error
• If no $select is specified, all allowed fields are returned

$filter - filtering data ​

Filter results based on conditions:

Basic syntax ​

$filter=field operator value

Portway supports comparison operators (eq, ne, gt, ge, lt, le), logical operators (and, or, not) and the string functions contains, startswith and endswith. Filter operations carries the full operator reference, the type-specific rules and the performance notes.

Filter examples ​

# Exact match
GET /api/prod/Products?$filter=ItemCode eq 'PROD001'

# Numeric comparison
GET /api/prod/Products?$filter=Price gt 50.00

# String contains
GET /api/prod/Products?$filter=contains(Description,'Widget')

# Multiple conditions
GET /api/prod/Products?$filter=Price gt 100 and Assortment eq 'Electronics'

# Complex filter
GET /api/prod/Products?$filter=(Price gt 100 and Price lt 500) or contains(Description,'Special')

$orderby - sorting results ​

Sort results by one or more fields:

Syntax ​

$orderby=field [asc|desc]

Examples ​

# Single field ascending (default)
GET /api/prod/Products?$orderby=Name

# Single field descending
GET /api/prod/Products?$orderby=Price desc

# Multiple fields
GET /api/prod/Products?$orderby=Category,Price desc

# Complex sorting
GET /api/prod/Products?$orderby=Category asc,Price desc,Name asc

Sorting rules ​

• Default sort order is ascending
• Use desc for descending order
• Multiple fields are sorted in order listed
• Field names are case-sensitive

$top and $skip - pagination ​

Control result set size and implement pagination:

$top syntax ​

$top=number

$skip syntax ​

$skip=number

Pagination examples ​

# First 10 items
GET /api/prod/Products?$top=10

# Skip first 20 items
GET /api/prod/Products?$skip=20

# Page 2 with 10 items per page
GET /api/prod/Products?$top=10&$skip=10

# Page 3 with 25 items per page
GET /api/prod/Products?$top=25&$skip=50

Always include $orderby when paginating to ensure consistent results across pages. Use the NextLink in the response for easy sequential navigation.

$count - total result count ​

When you're paginating, it helps to know how many rows match in total, not just how many came back on this page. Adding $count=true asks Portway to run an additional COUNT query with the same $filter, and the result arrives as a totalCount property in the response:

GET /api/prod/Products?$filter=Price gt 100&$top=10&$count=true

{
  "success": true,
  "count": 10,
  "totalCount": 342,
  "value": [ "..." ]
}

A few things worth knowing:

• count is the number of items on this page; totalCount is the unpaged total that matches your filter
• $top, $skip, $select, and $orderby have no effect on totalCount; only $filter shapes it
• The extra COUNT query only runs when you ask for it, so requests without $count pay nothing
• totalCount is omitted from the response entirely when $count is not requested

Since the count is one more round-trip to your database, it's most useful on the first page of a listing; subsequent pages can usually reuse it.

$expand - related data ​

$expand pulls a related entity into the response in the same request, nested under the navigation name:

GET /api/prod/Products?$expand=Category

The relationship is declared once in the endpoint's entity.json, and Portway turns it into a SQL JOIN. It applies to SQL Table and View endpoints, covers to-one navigations, and reuses the target's own column allowlist. The full contract, configuration and limits live in Expanding Related Data.

Combining query options ​

Multiple query options can be combined in a single request:

# Complete query example
GET /api/prod/Products
  ?$select=ItemCode,Description,Price,Category
  &$filter=Price gt 50 and Category eq 'Electronics'
  &$orderby=Price desc
  &$top=20
  &$skip=0

Data types in queries ​

Literal values follow the OData conventions: strings in single quotes with '' as the escape, numbers unquoted, dates in ISO 8601, and true or false in lowercase.

$filter=Name eq 'It''s a product'
$filter=Price gt 99.99
$filter=CreatedDate gt 2024-01-01T00:00:00Z
$filter=IsActive eq true

Filter operations covers each type in detail, including null handling.

Special characters and encoding ​

URL encoding ​

Special characters need to be URL encoded:

Reserved characters ​

These characters have special meaning in OData:

• $ - Query option prefix
• ( ) - Function and grouping
• ' - String delimiter
• , - List separator

Query response format ​

Successful queries return a JSON response:

{
  "Count": 50,
  "Value": [
    {
      "ItemCode": "PROD001",
      "Description": "Widget A",
      "Price": 99.99
    },
    {
      "ItemCode": "PROD002",
      "Description": "Widget B",
      "Price": 149.99
    }
  ],
  "NextLink": "/api/prod/Products?$top=10&$skip=20"
}

Response properties ​

Common query patterns ​

Search by text ​

# Contains search
GET /api/prod/Products?$filter=contains(Description,'widget')

# Starts with search
GET /api/prod/Products?$filter=startswith(Name,'A')

Date range queries ​

# Records created this year
GET /api/prod/Orders?$filter=CreatedDate ge 2024-01-01T00:00:00Z

# Records in date range
GET /api/prod/Orders?$filter=OrderDate ge 2024-01-01 and OrderDate lt 2024-02-01

Null checking ​

# Find unassigned items
GET /api/prod/Tasks?$filter=AssignedTo eq null

# Find completed items
GET /api/prod/Tasks?$filter=CompletedDate ne null

Complex filters ​

# Multiple conditions with grouping
GET /api/prod/Products
  ?$filter=(Price gt 100 and Price lt 500) and 
           (Category eq 'Electronics' or Category eq 'Computers')

Query limitations ​

Maximum values ​

Performance considerations ​

1. Use indexed fields in filters and sorting
2. Limit result sets with $top
3. Avoid complex string operations on large datasets
4. Use specific filters rather than post-filtering

Error responses ​

Query syntax errors ​

{
  "error": "Invalid filter syntax",
  "details": "Unknown operator 'equals' at position 15",
  "success": false
}

Invalid field names ​

{
  "error": "Invalid field name",
  "details": "Field 'InvalidField' is not allowed",
  "success": false
}

Type mismatch ​

{
  "error": "Type mismatch",
  "details": "Cannot compare string field 'Name' with numeric value",
  "success": false
}

Related topics ​

• Filter Operations
• Sorting & Pagination
• SQL Endpoints
• API Overview