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
        statuses {
          statusId
          statusName
          count
        }
        allTags {
          tagId
          tagName
        }
      }
      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, including linked commodity InventoryItem SKU, product name, description, model number, and custom values
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, contact.customValues.*
orderCarriers(none)contactId

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

Examples:

  • orderEntities[entityType:Shipper].contactId — group orders by shipper contact.
  • orderEntities[entityType:Shipper].contact.customValues.region — group orders by shipper's custom "region" value (deep path through contact).

Search linked inventory items​

The search argument uses the same order quick-search predicate as the orders list. In addition to order number, bill-to, carriers, tracking numbers, order entities, commodity descriptions, and commodity tracking numbers, order grouping now matches InventoryItem data linked through order commodities and child/container commodities. Search terms can match:

  • Inventory item sku
  • Inventory item productName
  • Inventory item description
  • Inventory item modelNumber
  • Inventory item customValues such as color, size, or other warehouse attributes
query {
  orderGroupBy(
    organizationId: 1
    groupBy: "OrderType"
    search: "denim-sku"
  ) {
    totalCount
    groups {
      keys
      count
      orders(take: 10) {
        items {
          orderId
          orderNumber
          trackingNumber
        }
      }
    }
  }
}

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
statuses[OrderGroupByStatus]?Status breakdown within this group
allTags[OrderGroupByTag]?All unique tags across orders in this group

OrderGroupByStatus​

FieldTypeDescription
statusIdIntOrder status ID
statusNameStringOrder status display name
countIntNumber of orders with this status in the group

OrderGroupByTag​

FieldTypeDescription
tagIdIntTag ID
tagNameStringTag display name
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
      }
    }
  }
}