Do not sell or share my personal information Skip to content

HPOS order querying APIs

With the introduction of HPOS, we’ve enhanced the querying functionality in WC. Now, in addition to the well-known existing APIs, we’re adding a few features that would make it easier to create complex queries on orders or their properties, including the ability to query custom order metadata.

All the new query types are implemented as additional query arguments that can be passed to wc_get_orders() and are heavily inspired by similar functionality in WordPress’ own WP_Query. As regular query arguments, they can be combined with other arguments to produce complex queries that, until now, would have required writing custom code and SQL.

The new query types

Metadata queries (meta_query)

With the introduction of HPOS, order fields that were previously stored as post metadata have been moved to their own tables, but the remaining metadata (custom, or from other extensions) can now be queried through the meta_query argument.

At its core, meta_query is an array that can contain one or more arrays with keys: key (the meta key name), value (the meta value) compare (optional) an operator to use for comparison purposes such as LIKE, RLIKE, NOT BETWEEN, BETWEEN, etc. type to cast the meta value to a specific SQL type in the query

The different arrays can also be combined using relation (which admits ‘AND’ or ‘OR’ values) to produce more complex queries. The syntax for this new argument is exactly the same as for WP_Query’s meta_query. As such, you can refer to the meta_query docs for more details.

		// Example: obtain all orders which have metadata with the "color" key (any value) and have metadata
// with key "size" containing "small" (so it'd match "extra-small" as well as "small", for example).
$orders = wc_get_orders(
    array(
        'meta_query' => array(
            array(
                'key' => 'color',
            ),
            array(
                'key'        => 'size',
                'value'      => 'small',
                'compare'    => 'LIKE'
            ),
        ),
    )
);

Order field queries (field_query)

This query type has a syntax similar to that of meta queries (meta_query) but instead of key you’d use field inside the different clauses. Here, field refers to any order property (such as billing_first_name, total or order_key, etc.) which are also accessible as top-level keys in the query arguments as usual. The difference between directly querying those properties and using a field_query is that you can create more complex queries by implementing comparison operators and combining or nesting.

		// Example. For a simple query, you'd be better off by using the order properties directly, even though there's a `field_query` equivalent.
$orders = wc_get_orders(
    array(
        'billing_first_name' => 'Lauren',
        'order_key'          => 'my_order_key',
    )
);

$orders = wc_get_orders(
    array(
        'field_query' => array(
            array(
                'field' => 'billing_first_name',
                'value' => 'Lauren'
            ),
            array(
                'field' => 'order_key',
                'value' => 'my_order_key',
            )
        )
    )
);

The true power of field_query is revealed when you want to perform more interesting queries, that were not possible before…

		// Example. Obtain all orders where either the total or shipping total is less than 5.0.

$orders = wc_get_orders(
    array(
        'field_query' => array(
            'relation' => 'OR',
            array(
                'field'   => 'total',
                'value'   => '5.0',
                'compare' => '<',
            ),
            array(
                'field'   => 'shipping_total',
                'value'   => '5.0',
                'compare' => '<',
            ),
        )
    )
);

Date queries (date_query)

Date queries allow you to fetch orders by querying their associated dates (date_completed, date_created, date_updated, date_paid) in various ways. The syntax for date_query is fully compatible with that of date_query in WP_Query. As such, a good source of examples and details is the meta_query docs in the WP codex.

		// Example. Obtain all orders paid in the last month that were created before noon (on any date).

$orders = wc_get_orders(
    array(
        'date_query' => array(
            'relation' => 'AND',
            array(
                'column'  => 'date_created_gmt',
                'hour'    => 12,
                'compare' => '<'
            ),
            array(
                'column'  => 'date_paid_gmt',
                'after'   => '1 month ago',
            ),
        ),
    )
);

Advanced Examples

		// Obtain orders either "on-hold" or "pending" that have metadata `weight` >= 50 and metadata `color` or `size` is set.

$query_args = array(
    'status' => array( 'pending', 'on-hold' ),
    'meta_query' => array(
        array(
            'key'     => 'weight',
            'value'   => '50',
            'compare' => '>=',
        ),
        array(
            'relation' => 'OR',
            array(
                'key'     => 'color',
                'compare' => 'EXISTS',
            ),
            array(
                'key'     => 'size',
                'compare' => 'EXISTS',
            )
        ),
    )
);

$orders = wc_get_orders( $query_args );

	
		// Obtain orders where the first name in the billing details contains "laur" (so it'd both match "lauren" and "laura", for example), and where the order's total is less than 10.0 and the total discount is >= 5.0.

$orders = wc_get_orders(
    array(
        'field_query' => array(
            array(
                'field'   => 'billing_first_name',
                'value'   => 'laur',
                'compare' => 'LIKE',
            ),
            array(
                'relation' => 'AND',
                array(
                    'field'   => 'total',
                    'value'   => '10.0',
                    'compare' => '<',
                    'type'    => 'NUMERIC',
                ),
                array(
                    'field'   => 'discount_total',
                    'value'   => '5.0',
                    'compare' => '>=',
                    'type'    => 'NUMERIC',
                )
            )
        ),
    )
);

Last updated: May 22, 2024