Join Expressions

Join expressions allow you to sort and filter by properties of related entities that are referenced through custom value foreign keys. This is useful when a custom field stores an ID that points to another entity (such as a contact, order, or country), and you want to sort or filter by a property on that related entity rather than by the raw ID value.

Syntax

customValues.key->entity.property

Part	Description
customValues.key	Path to the JSONB field containing the foreign key value
->	Join operator
entity	Alias of the target entity (case-insensitive)
property	Single-level property on the target entity to sort/filter by

Example

customValues.carrierId->contact.name

This reads the carrierId value from the entity's customValues, joins to the Contacts table by matching ContactId, and resolves to the contact's Name property.

Supported Entities

Alias	Entity	Join Key	Key Type	DbSet
contact	Contact	ContactId	int	Contacts
order	Order	OrderId	int	Orders
modeOfTransportation	ModeOfTransportation	ModeOfTransportationId	int	ModeOfTransportations
country	Country	CountryCode	string	Countries
terminal	Terminal	TerminalId	int	Terminals
contactAddress	ContactAddress	ContactAddressId	int	ContactAddresses

Entity aliases are case-insensitive (Contact, contact, and CONTACT all resolve to the same entity).

How It Works

The join expression builds a correlated subquery at the database level. For example:

customValues.carrierId->contact.name

Translates to the equivalent of:

(SELECT c."Name"
 FROM "Contacts" c
 WHERE c."ContactId" = jsonb_extract_path_int(x."CustomValues", 'carrierId')
 LIMIT 1)

The steps are:

1. Extract the key from the JSONB customValues column using the appropriate extraction method (JsonbExtractPathInt for integer keys, JsonbExtractPathText for string keys)
2. Query the target DbSet with a Where clause matching the entity's primary key to the extracted value
3. Select the target property (e.g., Name)
4. Wrap value types in Nullable — if the target property is a non-nullable value type (e.g., int), the result is cast to its nullable equivalent (int?) so that NULL is returned when no row matches instead of the type's default value
5. Take the first result using FirstOrDefault()

Sorting with Join Expressions

Use join syntax in the orderBy parameter:

query {
  orders(
    organizationId: 1
    orderBy: "customValues.carrierId->contact.name"
  ) {
    items { orderId }
  }
}

Location custom values can sort by contact-address names when they store a ContactAddressId:

orderBy: "customValues.deliveryLocationId->contactAddress.name"
orderBy: "customValues.returnLocationId->contactAddress.name"

Descending sort:

orderBy: "-customValues.carrierId->contact.name"

Multi-field sort with joins:

orderBy: "-created,customValues.carrierId->contact.name"

Terminal references stored in custom values can be sorted the same way:

orderBy: "customValues.terminalId->terminal.name"
orderBy: "-customValues.returnLocationId->terminal.name"

Filtering with Join Expressions

Use join syntax in the filter parameter:

query {
  eventDefinitions(
    organizationId: 1
    filter: "customValues.carrierId->contact.name:Acme*"
  ) {
    items { eventDefinitionId, eventName }
  }
}

All standard filter operators work with join expressions:

# Exact match
filter: "customValues.carrierId->contact.name:Acme Corp"

# Contact address reference
filter: "customValues.deliveryLocationId->contactAddress.name:Warehouse*"

# Wildcard
filter: "customValues.carrierId->contact.name:*express*"

# Negation
filter: "-customValues.countryCode->country.name:United States"

# NULL / NOT NULL on joined properties
filter: "customValues.carrierId->contact.contactId:NULL"
filter: "NOT customValues.carrierId->contact.contactId:NULL"

# Combined with other filters
filter: "status:Active AND customValues.carrierId->contact.name:Acme*"

# Terminal custom-value references
filter: "customValues.terminalId->terminal.name:Chicago*"
filter: "NOT customValues.returnLocationId->terminal.terminalId:NULL"

NULL Filtering on Join Expressions

NULL and NOT NULL checks work on join expressions, including when the joined property is a value type (such as an integer ID). When the custom value key is missing or null, the join subquery returns NULL regardless of the target property's type, so both of the following patterns behave correctly:

# Rows where the joined contact exists
filter: "NOT customValues.carrierId->contact.contactId:NULL"

# Rows where the joined contact does not exist (key is missing or null)
filter: "customValues.carrierId->contact.contactId:NULL"

Restrictions

• Single-level property access only: contact.name is valid; contact.address.city is not. Nested target properties (containing additional dots) are rejected.
• Requires database context: Join expressions require IApplicationDbContext to be passed to the sort/filter methods. GraphQL queries that support join syntax (such as getOrders and getEventDefinitions) provide this automatically.
• Unknown aliases throw errors: If the entity alias is not registered in the JoinableEntityRegistry, a ValidationException is returned.

Supported Queries

The following GraphQL queries support join sort and filter syntax:

Query	Sort	Filter
getOrders	Yes	Yes
getEventDefinitions	Yes	Yes

Related Topics

• Sorting Expressions - Full sorting syntax reference
• Filter Expressions - Lucene-based filter syntax reference
• Custom Fields - Custom value storage and naming