Related Orders

CXTMS tracks relationships between orders through shared commodities. Two orders are related when they share any commodity on the same root-to-leaf path of the container tree — meaning one order's commodity is an ancestor, equal to, or descendant of a commodity owned by another order.

All related-order fields are available on the Order type and support optional filtering and sorting.

Fields

Field	Returns	DataLoader
relatedOrders	[Order]	V1 (vw_order_related_orders view)
relatedOrder	Order	V1 — first result only
relatedOrdersV2	[Order]	V2 (parameterized recursive CTE)
relatedOrderV2	Order	V2 — first result only

Obsolete field

allRelatedOrders is a legacy alias for relatedOrders and should not be used in new queries. Use relatedOrders (V1) or relatedOrdersV2 (V2) instead.

Arguments

All four fields accept the same arguments:

Argument	Type	Required	Description
filter	String	No	Lucene filter applied to the related orders at the database level
orderBy	String	No	Sort expression (e.g. orderNumber asc, created desc). Defaults to orderNumber

relatedOrders (V1)

Returns all orders related to the parent order. Uses the AllRelatedOrdersDataLoader, which resolves commodity-sharing pairs through the vw_order_related_orders database view.

query {
  getOrders(organizationId: 1, take: 5) {
    items {
      orderId
      orderNumber
      relatedOrders(filter: "orderType:Shipment", orderBy: "created desc") {
        orderId
        orderNumber
        orderType
      }
    }
  }
}

relatedOrder (V1)

Returns only the first related order matching the filter and sort criteria. Backed by the same V1 DataLoader as relatedOrders; identical performance characteristics.

query {
  getOrders(organizationId: 1, take: 10) {
    items {
      orderId
      orderNumber
      relatedOrder(orderBy: "created desc") {
        orderId
        orderNumber
        created
      }
    }
  }
}

relatedOrdersV2

Returns all orders related to the parent order. Uses AllRelatedOrdersDataLoaderV2, which resolves commodity-sharing pairs via a parameterized recursive CTE seeded from the input order IDs.

How V2 differs from V1

Aspect	V1	V2
Pair-resolution mechanism	vw_order_related_orders view	Parameterized recursive CTE
Scope of DB scan	Entire Commodities table	Only commodities reachable from the input orders
Cost growth	With DB size	With input size
Result shape	Identical	Identical

Recursive CTE logic

The CTE walks the commodity container tree in both directions from the input orders:

1. seed — (OrderId, CommodityId) pairs for commodities directly attached to the input orders.
2. descendants — walks down from each seed pair, collecting every commodity nested inside an input order's seed commodity.
3. ancestors — walks up from each seed pair, collecting every container that holds an input order's seed commodity.
4. reachable — union of descendants and ancestors. For each input order I and commodity C in reachable[I], if any other non-draft order O directly owns C, then (I, O) is a related pair.

Draft orders are excluded from the result set. Cycles are prevented by the acyclic commodity-tree domain invariant and naturally deduped via UNION.

query {
  getOrders(organizationId: 1, take: 5) {
    items {
      orderId
      orderNumber
      relatedOrdersV2(filter: "orderType:Shipment", orderBy: "created desc") {
        orderId
        orderNumber
        orderType
      }
    }
  }
}

relatedOrderV2

Returns only the first related order matching the filter and sort criteria, using the V2 DataLoader. The orderBy argument determines which order is "first".

query {
  getOrders(organizationId: 1, take: 10) {
    items {
      orderId
      orderNumber
      relatedOrderV2(orderBy: "created desc") {
        orderId
        orderNumber
        created
      }
    }
  }
}

DataLoader batching

All four fields share the same batching strategy. Requests within a single GraphQL operation that have identical (filter, orderBy) arguments are grouped into a single database round-trip:

1. Resolve (OrderId, RelatedOrderId) pairs for all batched orders (one DB call per DataLoader version).
2. Group the pair-resolution results by (filter, orderBy) and fetch the matching Order rows once per unique combination.
3. Project Order → OrderGqlDto using AutoMapper and return the results to each caller.

This means fetching related orders for 50 orders in a list view costs the same as fetching them for 1 — as long as the filter and sort arguments are consistent.

Choosing V1 vs V2

Use V2 (relatedOrdersV2 / relatedOrderV2) for new integrations and in scenarios with large commodity tables, where the parameterized CTE gives a significant performance advantage. V1 fields remain available for backwards compatibility while V2 is validated in production.

Related topics

• Filter syntax — Lucene filter expressions
• Sorting — orderBy conventions