Skip to main content

Order Group By

The orderGroupBy query provides multi-field grouping of orders with aggregate summaries. It's designed for analytics dashboards, pivot-table views, and report builders that need to group orders by any combination of native fields, custom values, and collection properties.

Query Signature​

query OrderGroupBy(
  $organizationId: Int!
  $groupBy: String!
  $filter: String
  $search: String
  $includeDraft: Boolean
) {
  orderGroupBy(
    organizationId: $organizationId
    groupBy: $groupBy
    filter: $filter
    search: $search
    includeDraft: $includeDraft
  ) {
    totalCount
    groups {
      keys
      count
      summary {
        totalWeight
        totalPieces
        totalQuantity
        totalIncome
        totalExpense
        totalProfit
      }
      orders(take: 10, orderBy: "created desc") {
        items {
          orderId
          trackingNumber
        }
        totalCount
      }
    }
  }
}

Parameters​

ParameterTypeRequiredDefaultDescription
organizationIdIntYes—Organization to query
groupByStringYes—Comma-separated list of field specifications (max 5)
filterStringNo—Lucene filter applied to orders
searchStringNo—Full-text search applied to orders
includeDraftBooleanNofalseInclude draft orders in results

groupBy Field Specifications​

The groupBy parameter accepts a comma-separated list of field specifications. Each field can be one of three types:

Native Fields​

Direct order table columns. Supported values:

FieldDescription
OrderStatusIdOrder status
DivisionIdDivision
OrderTypeOrder type
BillToContactIdBill-to contact
EmployeeContactIdAssigned employee
SalespersonContactIdSalesperson
EquipmentTypeIdEquipment type
OrganizationIdOrganization

Custom Value Fields​

JSONB custom values using dot notation:

customValues.<key>

Keys must match [a-zA-Z0-9_.]. Examples: customValues.Region, customValues.ServiceLevel.

Collection Fields​

Sub-entity lookups using bracket filter syntax:

<collection>[<filterColumn>:<filterValue>].<selectColumn>

Supported collections:

CollectionFilter ColumnsSelect Columns
orderEntitiesentityTypecontactId, contactAddressId, nonContactName, entityType
orderCarriers(none)contactId

The entityType filter value maps to the EntityTypes enum (case-insensitive): Shipper, Consignee, PickupLocation, DeliveryLocation, etc.

Example: orderEntities[entityType:Shipper].contactId — group orders by shipper contact.

Response Types​

OrderGroupByResult​

FieldTypeDescription
totalCountIntNumber of groups
groups[OrderGroupBy]Array of groups

OrderGroupBy​

FieldTypeDescription
keysMap<String, Object>Key-value pairs for each groupBy field
countIntNumber of orders in this group
summaryOrderGroupBySummary?Aggregate summary (only computed when selected)
ordersCollectionSegment<Order>Paginated orders within this group

The orders field accepts optional orderBy (String), take (Int, default 20), and skip (Int) arguments for pagination within each group bucket.

OrderGroupBySummary​

FieldTypeDescription
totalWeightDecimalSum of order weights
totalPiecesDecimalSum of pieces
totalQuantityDecimalSum of quantities
totalIncomeDecimalSum of income charges
totalExpenseDecimalSum of expense charges
totalProfitDecimalIncome minus expense
Performance

The summary fields are only computed when explicitly selected in the GraphQL query. If you only need counts, omit the summary selection to avoid the join to the vw_order_summary view.

Examples​

Group orders by status​

query {
  orderGroupBy(
    organizationId: 1
    groupBy: "OrderStatusId"
  ) {
    totalCount
    groups {
      keys
      count
    }
  }
}

Multi-field grouping with summary​

query {
  orderGroupBy(
    organizationId: 1
    groupBy: "OrderStatusId,OrderType"
    filter: "orderStatus.name:Active"
  ) {
    groups {
      keys
      count
      summary {
        totalIncome
        totalExpense
        totalProfit
      }
    }
  }
}

Group by shipper with drill-down​

query {
  orderGroupBy(
    organizationId: 1
    groupBy: "orderEntities[entityType:Shipper].contactId"
  ) {
    groups {
      keys
      count
      orders(take: 5, orderBy: "created desc") {
        items {
          orderId
          trackingNumber
          created
        }
        totalCount
      }
    }
  }
}

Group by custom value​

query {
  orderGroupBy(
    organizationId: 1
    groupBy: "customValues.Region,OrderStatusId"
  ) {
    groups {
      keys
      count
      summary {
        totalWeight
        totalPieces
      }
    }
  }
}