WooCommerce REST API - Filtering Orders

WordPress provides various functions, known as hooks, enabling us to extend its capabilities and intervene in certain situations.

Using WordPress and the WooCommerce plugin—which transforms WordPress into a fully functional e-commerce platform—we can access a wide range of information via the REST API through endpoints in JSON format. I briefly discussed what the WooCommerce API is and how to use it in the article. Recently, based on an email I received, I needed to examine the Orders hooks. Below, I will explain the information I obtained through various examples.

Orders (Order)

The WOO API offers the /wp-json/wc/v3/orders/ endpoint to enable remote access to order operations.

curl -X GET https://alanadi.com/wp-json/wc/v3/orders \
    -u consumer_key:consumer_secret \
    -H "Content-Type: application/json"

This allows us to view order details—including purchase information, invoice, shipping address, payment method, order status, products in the order, and more—alongside. Additionally, we can also pass these details to a different service to create orders that are then synchronized into the WooCommerce content.

curl -X POST https://alanadi.com/wp-json/wc/v3/orders \
    -u consumer_key:consumer_secret \
    -H "Content-Type: application/json" \
    -d '{
  "payment_method": "bacs",
  "payment_method_title": "Direct Bank Transfer",
  "set_paid": true,
  "billing": {
    "first_name": "John",
    "last_name": "Doe",
    "address_1": "969 Market",
    "address_2": "",
    "city": "San Francisco",
    "state": "CA",
    "postcode": "94103",
    "country": "US",
    "email": "john.doe@alanadi.com",
    "phone": "(555) 555-5555"
  },
  "shipping": {
    "first_name": "John",
    "last_name": "Doe",
    "address_1": "969 Market",
    "address_2": "",
    "city": "San Francisco",
    "state": "CA",
    "postcode": "94103",
    "country": "US"
  },
  "line_items": [
    {
      "product_id": 93,
      "quantity": 2
    },
    {
      "product_id": 22,
      "variation_id": 23,
      "quantity": 1
    }
  ],
  "shipping_lines": [
    {
      "method_id": "flat_rate",
      "method_title": "Flat Rate",
      "total": 10
    }
  ]
}'

The /wp-json/wc/v3/orders endpoint orders by order ID (and thus chronologically from newest to oldest). The direction of this sorting can be changed using &order=asc.

Let’s now focus on the main topic.

To perform operations via parameters through the existing endpoint for orders, we use the woocommerce_rest_orders_prepare_object_query hook.

apply_filters( 'woocommerce_rest_orders_prepare_object_query', array $args, WP_REST_Request $request )

For example, using the parameter modified_after, orders can be filtered by the date they were last modified. The order dates are shown using date_created and date_modified¹.

add_filter('woocommerce_rest_orders_prepare_object_query', function(array $args, \WP_REST_Request $request) {
    $modified_after = $request->get_param('modified_after');

if (!$modified_after) return $args;

$args[‘date_query’][0][‘column’] = ‘post_modified’; $args[‘date_query’][0][‘after’] = $modified_after;

return $args;

}, 10, 2);

So, how can we retrieve orders that contain only specific products (_line_items > product_id_)?

We can achieve this using `$wpdb`[^2].

```php
add_filter('woocommerce_rest_orders_prepare_object_query', function (array $args, \WP_REST_Request $request) {

  global $wpdb;
  
  $product = $request->get_param('product');
  if (!$product) return $args;

  $order_ids = $wpdb->get_col(
    $wpdb->prepare("
      SELECT order_id
      FROM {$wpdb->order_items}
      WHERE order_item_id IN (SELECT order_item_id FROM {$wpdb->order_itemmeta} WHERE meta_key = '_product_id' AND meta_value = %d)
      AND order_item_type = 'line_item'",
      $request['product']
    )
  );

  if(!empty($args['post__in'])) $args['post__in'] = array_unique(array_merge($args['post__in'], $order_ids));
  else $args['post__in'] = $order_ids;

  return $args;
}, 10, 2);

If you’re using a multi-vendor plugin such as Dokan², you may wish to filter orders at the vendor (seller) level and/or share these endpoints with relevant vendors. In this case, we’ll need to include the id and store name fields from the tables created by the plugin³.

So, how can we achieve this?

add_filter("woocommerce_rest_orders_prepare_object_query", function (array $args, \WP_REST_Request $request) {

  global $wpdb;

  $storeid = $request->get_param('storeid');
  $storename = $request->get_param('storename');

  if (!$storeid && !$storename) return $args;

  $getVendorName = $wpdb->get_col(
    $wpdb->prepare("
      SELECT user_id FROM {$wpdb->usermeta} 
      WHERE meta_key = 'dokan_store_name' AND meta_value = '".esc_attr($storename)."'"
    )
  );

  $post_ids = $wpdb->get_col(
    $wpdb->prepare("
      SELECT DISTINCT p1.post_id
      FROM {$wpdb->postmeta} p1
      INNER JOIN {$wpdb->postmeta} p2 ON p1.post_id = p2.post_id
      WHERE
        ( p1.meta_key = '_dokan_vendor_id' AND p1.meta_value = ".esc_attr( $storeid ?: $getVendorName[0] )." )
      "
    )
  );

  if(!empty($args['post__in'])) $args['post__in'] = array_unique(array_merge($args['post__in'], $post_ids));
  else $args['post__in'] = $post_ids;

  return $args;
}, 10, 2);

We could also expand on these and similar examples. Especially if you have any specific topic of interest, I’ll try to include it in the examples.

Footnotes

1. WooCommerce REST API - Filter Orders By Date Modified ↩
2. Dokan - Multi-vendor Marketplace Plugin ↩
3. Rest API: Get all orders of a single vendor ↩