sales_order_item Table

Last update: July 24, 2023

CREATED FOR:

  • Beginner
  • Intermediate
  • Admin
  • Developer
  • User

The sales_order_item table (sales_flat_order_item on M1) contains records of all products that were purchased in an order. Each row represents a unique sku included in an order. The quantity of units that were purchased for a specific sku is most often represented by the qty_ordered field.

Product Types

The sales_order_item captures details on all product types that were purchased. A common practice in Adobe Commerce is to offer configurable products, or in other words, a product that can be customized according to size, color, and other product attributes. Although a configurable product has its own sku, it can relate to multiple simple products, where each simple product represents a unique product configuration. Refer to configuring products for more information.

For example, consider a configurable product such as a t-shirt. When a customer checks out, they select options to alter the color and size. If the customer selects a color of blue, and a size of small, they end up purchasing a simple product like t-shirt-blue-small which relates back to the parent product of t-shirt.

When a configurable product is included in an order, two rows are generated in the sales_order_item table: one for the simple sku and one for the configurable parent. These two records in the sales_order_item table can be related to each other through the following join:

  • (simple) sales_order_item.parent_item_id => (configurable) sales_order_item.item_id

Therefore it is possible to report on sales of products either at the simple level or at the configurable level. By default, all standard order-item-level metrics in Commerce Intelligence are configured to exclude the simple products, and only report on the configurable versions. This is accomplished through the Ordered products we count filter set, which filters on the condition where parent_item_id is NULL.

Common Columns

Column Name
Description
base_price
Price of an individual unit of a product at the time of sale after catalog price rules, tiered discounts, and special pricing are applied and before any taxes, shipping, or cart discounts are applied. This is represented in the base currency of the store.
created_at
Creation timestamp of the order item, stored locally in UTC. Depending on your configuration in Commerce Intelligence, this timestamp may be converted to a reporting time zone in Commerce Intelligence that differs from your database time zone.
item_id (PK)
Unique identifier for the table.
name
Text name of the order item.
order_id
Foreign key associated with the sales_order table. Join to sales_order.entity_id to determine order attributes associated with the order item.
parent_item_id
Foreign key that relates a simple product to its parent bundle or configurable product. Join to sales_order_item.item_id to determine parent product attributes associated with simple product. For parent order items (that is, bundle or configurable product types), the parent_item_id is NULL.
product_id
Foreign key associated with the catalog_product_entity table. Join to catalog_product_entity.entity_id to determine product attributes associated with the order item.
product_type
Type of product that was sold. Potential product types include: simple, configurable, grouped, virtual, bundle, and downloadable.
qty_ordered
Quantity of units included in the cart for the particular order item at the time of sale.
sku
Unique identifier for the order item that was purchased.
store_id
Foreign key associated with the store table. Join to store.store_id to determine which Commerce store view associated with the order item.