Order Queries

This document is for a version of Craft Commerce that is no longer supported. Please refer to the latest version →

You can fetch orders in your templates or PHP code using order queries.

{# Create a new order query #}
{% set myOrderQuery = craft.orders() %}

// Create a new order query
$myOrderQuery = \craft\commerce\elements\Order::find();

Once you’ve created an order query, you can set parameters on it to narrow down the results, and then execute it (opens new window) by calling .all(). An array of Order (opens new window) objects will be returned.

See Element Queries (opens new window) in the Craft docs to learn about how element queries work.

#Example

We can display an order with a given order number by doing the following:

Create an order query with craft.orders().
Set the number parameter on it.
Fetch the order with .one().
Output information about the order as HTML.

{# Get the requested order number from the query string #}
{% set orderNumber = craft.app.request.getQueryParam('number') %}

{# Create an order query with the 'number' parameter #}
{% set myOrderQuery = craft.orders()
  .number(orderNumber) %}

{# Fetch the order #}
{% set order = myOrderQuery.one() %}

{# Make sure it exists #}
{% if not order %}
  {% exit 404 %}
{% endif %}

{# Display the order #}
<h1>Order {{ order.getShortNumber() }}</h1>
<!-- ... ->

#Parameters

Order queries support the following parameters:

anyStatus
asArray
customer
customerId
dateCreated
dateOrdered
datePaid
dateUpdated
email
expiryDate
fixedOrder
gateway
gatewayId
hasPurchasables
hasTransactions
id
ignorePlaceholders
inReverse
isCompleted
isPaid
isUnpaid
limit
number
offset
orderBy
orderStatus
orderStatusId
preferSites
reference
relatedTo
search
shortNumber
trashed
uid
user
with

#`anyStatus`

Clears out the status() (opens new window) and enabledForSite() (opens new window) parameters.

{# Fetch all orders, regardless of status #}
{% set orders = craft.orders()
    .anyStatus()
    .all() %}

// Fetch all orders, regardless of status
$orders = \craft\commerce\elements\Order::find()
    ->anyStatus()
    ->all();

#`asArray`

Causes the query to return matching orders as arrays of data, rather than Order (opens new window) objects.

{# Fetch orders as arrays #}
{% set orders = craft.orders()
    .asArray()
    .all() %}

// Fetch orders as arrays
$orders = \craft\commerce\elements\Order::find()
    ->asArray()
    ->all();

#`customer`

Narrows the query results based on the customer.

Possible values include:

Value	Fetches orders…
a Customer (opens new window) object	with a customer represented by the object.

{# Fetch the current user's orders #}
{% set orders = craft.orders()
    .customer(currentUser.customerFieldHandle)
    .all() %}

// Fetch the current user's orders
$user = Craft::$app->user->getIdentity();
$orders = \craft\commerce\elements\Order::find()
    ->customer($user->customerFieldHandle)
    ->all();

#`customerId`

Narrows the query results based on the customer, per their ID.

Possible values include:

Value	Fetches orders…
`1`	with a customer with an ID of 1.
`'not 1'`	not with a customer with an ID of 1.
`[1, 2]`	with a customer with an ID of 1 or 2.
`['not', 1, 2]`	not with a customer with an ID of 1 or 2.

{# Fetch the current user's orders #}
{% set orders = craft.orders()
    .customerId(currentUser.customerFieldHandle.id)
    .all() %}

// Fetch the current user's orders
$user = Craft::$app->user->getIdentity();
$orders = \craft\commerce\elements\Order::find()
    ->customerId($user->customerFieldHandle->id)
    ->all();

#`dateCreated`

Narrows the query results based on the orders’ creation dates.

Possible values include:

Value	Fetches orders…
`'>= 2018-04-01'`	that were created on or after 2018-04-01.
`'< 2018-05-01'`	that were created before 2018-05-01
`['and', '>= 2018-04-04', '< 2018-05-01']`	that were created between 2018-04-01 and 2018-05-01.

{# Fetch orders created last month #}
{% set start = date('first day of last month')|atom %}
{% set end = date('first day of this month')|atom %}

{% set orders = craft.orders()
    .dateCreated(['and', ">= #{start}", "< #{end}"])
    .all() %}

// Fetch orders created last month
$start = (new \DateTime('first day of last month'))->format(\DateTime::ATOM);
$end = (new \DateTime('first day of this month'))->format(\DateTime::ATOM);

$orders = \craft\commerce\elements\Order::find()
    ->dateCreated(['and', ">= {$start}", "< {$end}"])
    ->all();

#`dateOrdered`

Narrows the query results based on the orders’ completion dates.

Possible values include:

Value	Fetches orders…
`'>= 2018-04-01'`	that were completed on or after 2018-04-01.
`'< 2018-05-01'`	that were completed before 2018-05-01
`['and', '>= 2018-04-04', '< 2018-05-01']`	that were completed between 2018-04-01 and 2018-05-01.

{# Fetch orders that were completed recently #}
{% set aWeekAgo = date('7 days ago')|atom %}

{% set orders = craft.orders()
    .dateOrdered(">= #{aWeekAgo}")
    .all() %}

// Fetch orders that were completed recently
$aWeekAgo = new \DateTime('7 days ago')->format(\DateTime::ATOM);

$orders = \craft\commerce\elements\Order::find()
    ->dateOrdered(">= {$aWeekAgo}")
    ->all();

#`datePaid`

Narrows the query results based on the orders’ paid dates.

Possible values include:

Value	Fetches orders…
`'>= 2018-04-01'`	that were paid on or after 2018-04-01.
`'< 2018-05-01'`	that were paid before 2018-05-01
`['and', '>= 2018-04-04', '< 2018-05-01']`	that were completed between 2018-04-01 and 2018-05-01.

{# Fetch orders that were paid for recently #}
{% set aWeekAgo = date('7 days ago')|atom %}

{% set orders = craft.orders()
    .datePaid(">= #{aWeekAgo}")
    .all() %}

// Fetch orders that were paid for recently
$aWeekAgo = new \DateTime('7 days ago')->format(\DateTime::ATOM);

$orders = \craft\commerce\elements\Order::find()
    ->datePaid(">= {$aWeekAgo}")
    ->all();

#`dateUpdated`

Narrows the query results based on the orders’ last-updated dates.

Possible values include:

Value	Fetches orders…
`'>= 2018-04-01'`	that were updated on or after 2018-04-01.
`'< 2018-05-01'`	that were updated before 2018-05-01
`['and', '>= 2018-04-04', '< 2018-05-01']`	that were updated between 2018-04-01 and 2018-05-01.

{# Fetch orders updated in the last week #}
{% set lastWeek = date('1 week ago')|atom %}

{% set orders = craft.orders()
    .dateUpdated(">= #{lastWeek}")
    .all() %}

// Fetch orders updated in the last week
$lastWeek = (new \DateTime('1 week ago'))->format(\DateTime::ATOM);

$orders = \craft\commerce\elements\Order::find()
    ->dateUpdated(">= {$lastWeek}")
    ->all();

#`email`

Narrows the query results based on the customers’ email addresses.

Possible values include:

Value	Fetches orders with customers…
`'foo@bar.baz'`	with an email of `foo@bar.baz`.
`'not foo@bar.baz'`	not with an email of `foo@bar.baz`.
`'*@bar.baz'`	with an email that ends with `@bar.baz`.

{# Fetch orders from customers with a .co.uk domain on their email address #}
{% set orders = craft.orders()
    .email('*.co.uk')
    .all() %}

// Fetch orders from customers with a .co.uk domain on their email address
$orders = \craft\commerce\elements\Order::find()
    ->email('*.co.uk')
    ->all();

#`expiryDate`

Narrows the query results based on the orders’ expiry dates.

Possible values include:

Value	Fetches orders…
`'>= 2020-04-01'`	that will expire on or after 2020-04-01.
`'< 2020-05-01'`	that will expire before 2020-05-01
`['and', '>= 2020-04-04', '< 2020-05-01']`	that will expire between 2020-04-01 and 2020-05-01.

{# Fetch orders expiring this month #}
{% set nextMonth = date('first day of next month')|atom %}

{% set orders = craft.orders()
    .expiryDate("< #{nextMonth}")
    .all() %}

// Fetch orders expiring this month
$nextMonth = new \DateTime('first day of next month')->format(\DateTime::ATOM);

$orders = \craft\commerce\elements\Order::find()
    ->expiryDate("< {$nextMonth}")
    ->all();

#`fixedOrder`

Causes the query results to be returned in the order specified by id.

{# Fetch orders in a specific order #}
{% set orders = craft.orders()
    .id([1, 2, 3, 4, 5])
    .fixedOrder()
    .all() %}

// Fetch orders in a specific order
$orders = \craft\commerce\elements\Order::find()
    ->id([1, 2, 3, 4, 5])
    ->fixedOrder()
    ->all();

#`gateway`

Narrows the query results based on the gateway.

Possible values include:

Value	Fetches orders…
a Gateway (opens new window) object	with a gateway represented by the object.

#`gatewayId`

Narrows the query results based on the gateway, per its ID.

Possible values include:

Value	Fetches orders…
`1`	with a gateway with an ID of 1.
`'not 1'`	not with a gateway with an ID of 1.
`[1, 2]`	with a gateway with an ID of 1 or 2.
`['not', 1, 2]`	not with a gateway with an ID of 1 or 2.

#`hasPurchasables`

Narrows the query results to only orders that have certain purchasables.

Possible values include:

Value	Fetches orders…
a PurchasableInterface (opens new window) object	with a purchasable represented by the object.
an array of PurchasableInterface (opens new window) objects	with all the purchasables represented by the objects.

#`hasTransactions`

Narrows the query results to only carts that have at least one transaction.

{# Fetch carts that have attempted payments #}
{% set orders = craft.orders()
    .hasTransactions()
    .all() %}

// Fetch carts that have attempted payments
$orders = \craft\commerce\elements\Order::find()
    ->hasTransactions()
    ->all();

#`id`

Narrows the query results based on the orders’ IDs.

Possible values include:

Value	Fetches orders…
`1`	with an ID of 1.
`'not 1'`	not with an ID of 1.
`[1, 2]`	with an ID of 1 or 2.
`['not', 1, 2]`	not with an ID of 1 or 2.

{# Fetch the order by its ID #}
{% set order = craft.orders()
    .id(1)
    .one() %}

// Fetch the order by its ID
$order = \craft\commerce\elements\Order::find()
    ->id(1)
    ->one();

This can be combined with fixedOrder if you want the results to be returned in a specific order.

#`ignorePlaceholders`

Causes the query to return matching orders as they are stored in the database, ignoring matching placeholder elements that were set by craft\services\Elements::setPlaceholderElement() (opens new window).

#`inReverse`

Causes the query results to be returned in reverse order.

{# Fetch orders in reverse #}
{% set orders = craft.orders()
    .inReverse()
    .all() %}

// Fetch orders in reverse
$orders = \craft\commerce\elements\Order::find()
    ->inReverse()
    ->all();

#`isCompleted`

Narrows the query results to only orders that are completed.

{# Fetch completed orders #}
{% set orders = craft.orders()
    .isCompleted()
    .all() %}

// Fetch completed orders
$orders = \craft\commerce\elements\Order::find()
    ->isCompleted()
    ->all();

#`isPaid`

Narrows the query results to only orders that are paid.

{# Fetch paid orders #}
{% set orders = craft.orders()
    .isPaid()
    .all() %}

// Fetch paid orders
$orders = \craft\commerce\elements\Order::find()
    ->isPaid()
    ->all();

#`isUnpaid`

Narrows the query results to only orders that are not paid.

{# Fetch unpaid orders #}
{% set orders = craft.orders()
    .isUnpaid()
    .all() %}

// Fetch unpaid orders
$orders = \craft\commerce\elements\Order::find()
    ->isUnpaid()
    ->all();

#`limit`

Determines the number of orders that should be returned.

{# Fetch up to 10 orders  #}
{% set orders = craft.orders()
    .limit(10)
    .all() %}

// Fetch up to 10 orders
$orders = \craft\commerce\elements\Order::find()
    ->limit(10)
    ->all();

#`number`

Narrows the query results based on the order number.

Possible values include:

Value	Fetches orders…
`'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'`	with a matching order number

{# Fetch the requested order #}
{% set orderNumber = craft.app.request.getQueryParam('number') %}
{% set order = craft.orders()
    .number(orderNumber)
    .one() %}

// Fetch the requested order
$orderNumber = Craft::$app->request->getQueryParam('number');
$order = \craft\commerce\elements\Order::find()
    ->number($orderNumber)
    ->one();

#`offset`

Determines how many orders should be skipped in the results.

{# Fetch all orders except for the first 3 #}
{% set orders = craft.orders()
    .offset(3)
    .all() %}

// Fetch all orders except for the first 3
$orders = \craft\commerce\elements\Order::find()
    ->offset(3)
    ->all();

#`orderBy`

Determines the order that the orders should be returned in.

{# Fetch all orders in order of date created #}
{% set orders = craft.orders()
    .orderBy('dateCreated asc')
    .all() %}

// Fetch all orders in order of date created
$orders = \craft\commerce\elements\Order::find()
    ->orderBy('dateCreated asc')
    ->all();

#`orderStatus`

Narrows the query results based on the order statuses.

Possible values include:

Value	Fetches orders…
`'foo'`	with an order status with a handle of `foo`.
`'not foo'`	not with an order status with a handle of `foo`.
`['foo', 'bar']`	with an order status with a handle of `foo` or `bar`.
`['not', 'foo', 'bar']`	not with an order status with a handle of `foo` or `bar`.
a OrderStatus (opens new window) object	with an order status represented by the object.

{# Fetch shipped orders #}
{% set orders = craft.orders()
    .orderStatus('shipped')
    .all() %}

// Fetch shipped orders
$orders = \craft\commerce\elements\Order::find()
    ->orderStatus('shipped')
    ->all();

#`orderStatusId`

Narrows the query results based on the order statuses, per their IDs.

Possible values include:

Value	Fetches orders…
`1`	with an order status with an ID of 1.
`'not 1'`	not with an order status with an ID of 1.
`[1, 2]`	with an order status with an ID of 1 or 2.
`['not', 1, 2]`	not with an order status with an ID of 1 or 2.

{# Fetch orders with an order status with an ID of 1 #}
{% set orders = craft.orders()
    .authorGroupId(1)
    .all() %}

// Fetch orders with an order status with an ID of 1
$orders = \craft\commerce\elements\Order::find()
    ->authorGroupId(1)
    ->all();

#`preferSites`

If unique() (opens new window) is set, this determines which site should be selected when querying multi-site elements.

For example, if element “Foo” exists in Site A and Site B, and element “Bar” exists in Site B and Site C, and this is set to ['c', 'b', 'a'], then Foo will be returned for Site C, and Bar will be returned for Site B.

If this isn’t set, then preference goes to the current site.

{# Fetch unique orders from Site A, or Site B if they don’t exist in Site A #}
{% set orders = craft.orders()
    .site('*')
    .unique()
    .preferSites(['a', 'b'])
    .all() %}

// Fetch unique orders from Site A, or Site B if they don’t exist in Site A
$orders = \craft\commerce\elements\Order::find()
    ->site('*')
    ->unique()
    ->preferSites(['a', 'b'])
    ->all();

#`reference`

Narrows the query results based on the order reference.

Possible values include:

Value	Fetches orders…
`'xxxx'`	with a matching order reference

{# Fetch the requested order #}
{% set orderReference = craft.app.request.getQueryParam('ref') %}
{% set order = craft.orders()
    .reference(orderReference)
    .one() %}

// Fetch the requested order
$orderReference = Craft::$app->request->getQueryParam('ref');
$order = \craft\commerce\elements\Order::find()
    ->reference($orderReference)
    ->one();

#`relatedTo`

Narrows the query results to only orders that are related to certain other elements.

See Relations (opens new window) for a full explanation of how to work with this parameter.

{# Fetch all orders that are related to myCategory #}
{% set orders = craft.orders()
    .relatedTo(myCategory)
    .all() %}

// Fetch all orders that are related to $myCategory
$orders = \craft\commerce\elements\Order::find()
    ->relatedTo($myCategory)
    ->all();

#`search`

Narrows the query results to only orders that match a search query.

See Searching (opens new window) for a full explanation of how to work with this parameter.

{# Get the search query from the 'q' query string param #}
{% set searchQuery = craft.app.request.getQueryParam('q') %}

{# Fetch all orders that match the search query #}
{% set orders = craft.orders()
    .search(searchQuery)
    .all() %}

// Get the search query from the 'q' query string param
$searchQuery = \Craft::$app->request->getQueryParam('q');

// Fetch all orders that match the search query
$orders = \craft\commerce\elements\Order::find()
    ->search($searchQuery)
    ->all();

#`shortNumber`

Narrows the query results based on the order short number.

Possible values include:

Value	Fetches orders…
`'xxxxxxx'`	with a matching order number

{# Fetch the requested order #}
{% set orderNumber = craft.app.request.getQueryParam('shortNumber') %}
{% set order = craft.orders()
    .shortNumber(orderNumber)
    .one() %}

// Fetch the requested order
$orderNumber = Craft::$app->request->getQueryParam('shortNumber');
$order = \craft\commerce\elements\Order::find()
    ->shortNumber($orderNumber)
    ->one();

#`trashed`

Narrows the query results to only orders that have been soft-deleted.

{# Fetch trashed orders #}
{% set orders = craft.orders()
    .trashed()
    .all() %}

// Fetch trashed orders
$orders = \craft\commerce\elements\Order::find()
    ->trashed()
    ->all();

#`uid`

Narrows the query results based on the orders’ UIDs.

{# Fetch the order by its UID #}
{% set order = craft.orders()
    .uid('xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx')
    .one() %}

// Fetch the order by its UID
$order = \craft\commerce\elements\Order::find()
    ->uid('xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx')
    ->one();

#`user`

Narrows the query results based on the customer’s user account.

Possible values include:

Value	Fetches orders…
`1`	with a customer with a user account ID of 1.
a User (opens new window) object	with a customer with a user account represented by the object.

{# Fetch the current user's orders #}
{% set orders = craft.orders()
    .user(currentUser)
    .all() %}

// Fetch the current user's orders
$user = Craft::$app->user->getIdentity();
$orders = \craft\commerce\elements\Order::find()
    ->user($user)
    ->all();

#`with`

Causes the query to return matching orders eager-loaded with related elements.

See Eager-Loading Elements (opens new window) for a full explanation of how to work with this parameter.

{# Fetch orders eager-loaded with the "Related" field’s relations #}
{% set orders = craft.orders()
    .with(['related'])
    .all() %}

// Fetch orders eager-loaded with the "Related" field’s relations
$orders = \craft\commerce\elements\Order::find()
    ->with(['related'])
    ->all();

Was this page helpful?

Parts of this page are generated or assembled by automations. While we greatly appreciate contributions to the documentation, reporting automated content issues will allow us to fix them at the source!

Order Queries

On this Page

#Example

#Parameters

#anyStatus

#asArray

#customer

#customerId

#dateCreated

#dateOrdered

#datePaid

#dateUpdated

#email

#expiryDate

#fixedOrder

#gateway

#gatewayId

#hasPurchasables

#hasTransactions

#id

#ignorePlaceholders

#inReverse

#isCompleted

#isPaid

#isUnpaid

#limit

#number

#offset

#orderBy

#orderStatus

#orderStatusId

#preferSites

#reference

#relatedTo

#search

#shortNumber

#trashed

#uid

#user

#with

Was this page helpful?

#`anyStatus`

#`asArray`

#`customer`

#`customerId`

#`dateCreated`

#`dateOrdered`

#`datePaid`

#`dateUpdated`

#`email`

#`expiryDate`

#`fixedOrder`

#`gateway`

#`gatewayId`

#`hasPurchasables`

#`hasTransactions`

#`id`

#`ignorePlaceholders`

#`inReverse`

#`isCompleted`

#`isPaid`

#`isUnpaid`

#`limit`

#`number`

#`offset`

#`orderBy`

#`orderStatus`

#`orderStatusId`

#`preferSites`

#`reference`

#`relatedTo`

#`search`

#`shortNumber`

#`trashed`

#`uid`

#`user`

#`with`