Posted inYes WooCommerce Plugins

Get WooCommerce Orders by Meta Data

March 24, 2022No Comments
Get WooCommerce Orders by Meta Data

Depending on your coding scenario there are a few different ways to get orders by meta data including,

  • wc_get_orders() function
  • using WordPress’s WP_Query class or
  • by using WordPress’s native class wpdb and using RAW SQL.

In this post I’ll give you examples of doing all 3 or these for common scenarios like getting them date, by single and multiple meta_data and by ACF Field.

Quick Links

Woocommerce NAtive Method
WordPress Native method
Raw sql method
Learn to customize WooCommerce Fully with an online Udemy course

Getting Orders by Meta Data with WooCommerce Native functions:

WooCommerce, being a comprehensive e-commerce platform, has its own functions you can use to achieve this. Ill give you several examples including ordering them by date and getting orders by multiple meta values:

Simple example:

You need to make two code additions in order to use WordPress’s native functions:

Step 1:

You need too explicitly tell WooCommerce to search by your chosen meta value by inserting this code into your functions.php file.

IMPORTANT: You do need to change the highlighted values below to match those of your specific custom meta value name.

function searchCustomMeta( $query, $query_vars ) {
	if ( ! empty( $query_vars['customMeta'] ) ) {
		$query['meta_query'][] = array(
			'key' => 'customMeta',
			'value' => esc_attr( $query_vars['customMeta'] ),
		);
	}

	return $query;
}
add_filter( 'woocommerce_order_data_store_cpt_get_orders_query', 'searchCustomMeta', 10, 2 );

Step 2:

In your theme or plugin, where ever you want to use it, you can now use the WooCommerce native function as below to get your orders by meta value. You will obviously want to replace the highlights customMeta below but also the ‘somevalue’.

$orders = wc_get_orders( array( 'customMeta' => 'someValue' ) );

Get WooCommerce Orders by Multiple Meta Values:

To get WooCommerce Orders by multiple meta values the procedure is very similar to the above ‘Simple Example’. You just need to add more code in very similar ways.

Step 1

Add this to your functions.php replacing the highlighted values as per your own meta values you are wanting to search for.

function searchMultipleMeta( $query, $query_vars ) {
	if ( ! empty( $query_vars['customMeta1'] ) ) {
		$query['meta_query'][] = array(
			'key' => 'customMeta1',
			'value' => esc_attr( $query_vars['customMeta1'] ),
		);
	}

        if ( ! empty( $query_vars['customMeta2'] ) ) {
		$query['meta_query'][] = array(
			'key' => 'customMeta2',
			'value' => esc_attr( $query_vars['customMeta2'] ),
		);
	}

	return $query;
}
add_filter( 'woocommerce_order_data_store_cpt_get_orders_query', 'searchMultipleMeta', 10, 2 );

Step 2:

$orders = wc_get_orders( 
    array( 
         'customMeta1' => 'someValue1',
         'customMeta2' => 'someValue2'
         ) 
);

Get WooCommerce Orders by Meta Value ordered by date:

In order to get orders by Date AND Meta Value we can easily use WooCommerce’s native functions which do this out of the box.

Step 1:

Add this to functions.php replacing ‘customMeta’ with your own meta value name:

function searchCustomMeta( $query, $query_vars ) {
	if ( ! empty( $query_vars['customMeta'] ) ) {
		$query['meta_query'][] = array(
			'key' => 'customMeta',
			'value' => esc_attr( $query_vars['customMeta'] ),
		);
	}

	return $query;
}
add_filter( 'woocommerce_order_data_store_cpt_get_orders_query', 'searchCustomMeta', 10, 2 );

Step 2:

Now you can use WoCommerce’s native function to get by both date and Meta Value.

PROTIP: You can get query various Order related dates, those being,

  • date_created,
  • date_modified,
  • date_completed,
  • date_paid

You can also various kinds of data base on these dates, for example,

  • Get orders from a specific date
  • Get orders greater than a specific date
  • Get orders greater than equal to a specific date
  • Get orders less than the a specific date
  • Get orders between a set of specific dates.

You can also use the above queries with either Timestamp or Standard Format.

Change the below code to have the date data you are interested in and or the specific query.

// this will get all orders created on 2016-02-12 which also have the 'customMeta' value of 'someValue'
$orders = wc_get_orders( 
    array( 
         'customMeta' => 'somevalue',
         'date_created' => '2016-02-12'
    ) 
);

Get WooCommerce Orders by ACF Field:

This is an easy one. ACF Fields save as ‘meta_data’ on a Post (which represents a WooCommerce order) Object so you can use the same method for retrieving an order by single or multiple meta_data described above.

Getting Orders by Meta Data with WordPress Native functions:

Get WooCommerce Orders by Meta Value:

This code block will get all orders (aka shop_orders in the WordPress database) that have a status of processing. -1 means to return all posts that there are available. Of course it also gets posts which have a specific meta data name (key) of ‘cars’ and values of Ferrari, Porsche and Toyota.

Note: The compare part of the meta_query has various options which you can read about here in the WordPress Developer Docs for this function.

The code then loops through the results and prints out the status of each one. Change the code within the loop and in the $loop variable as you desire.

$loop = new WP_Query( array(
		    'post_type'         => 'shop_order',
		    'post_status'       =>  array('processing'),
		    'posts_per_page'    => -1, // change to however many you want
                    'meta_query' => array(
		array(
			'key' => 'cars', // the name of the meta key
			'value' => array( 'Ferrari', 'Porsche', 'Toyota' ),
			'compare' => 'IN',
		)
	)
		) );

		// The WordPress post loop
		if ( $loop->have_posts() ): 
			while ( $loop->have_posts() ) : $loop->the_post();

			// do some code here for eg
                        echo $loop->post->post_status; // will print out the status of this order        eg 'processing' or 'complete' or whatever

			endwhile;

			wp_reset_postdata();

		endif;

Get WooCommerce Orders by multiple Meta Value:

$loop = new WP_Query( array(
		    'post_type'         => 'shop_order',
		    'post_status'       =>  array('processing'),
		    'posts_per_page'    => -1, // change to however many you want
                    'meta_query' => array(
        'relation' => 'AND',
        'state_clause' => array(
            'key' => 'state',
            'value' => 'Wisconsin',
        ),
        'city_clause' => array(
            'key' => 'city',
            'compare' => 'EXISTS',
        ), 
    )
		) );

		// The WordPress post loop
		if ( $loop->have_posts() ): 
			while ( $loop->have_posts() ) : $loop->the_post();

			// do some code here for eg
                        echo $loop->post->post_status; // will print out the status of this order        eg 'processing' or 'complete' or whatever

			endwhile;

			wp_reset_postdata();

		endif;

Get WooCommerce Orders by Meta Value and date:

There are a load of different ways to access date but below is one way. You can view more here in the WordPress Developer Docs.

$today = getdate();
$loop = new WP_Query( array(
		    'post_type'         => 'shop_order',
		    'post_status'       =>  array('processing'),
		    'posts_per_page'    => -1, // change to however many you want
'date_query' => array(
        array(
            'year'  => $today['year'],
            'month' => $today['mon'],
            'day'   => $today['mday'],
        ),
    ),
                    'meta_query' => array(
		array(
			'key' => 'cars', // the name of the meta key
			'value' => array( 'Ferrari', 'Porsche', 'Toyota' ),
			'compare' => 'IN',
		)
	)
		) );

		// The WordPress post loop
		if ( $loop->have_posts() ): 
			while ( $loop->have_posts() ) : $loop->the_post();

			// do some code here for eg
                        echo $loop->post->post_status; // will print out the status of this order        eg 'processing' or 'complete' or whatever

			endwhile;

			wp_reset_postdata();

		endif;

Get WooCommerce Orders by ACF Field:

This is an easy one. ACF Fields save as ‘meta_data’ on a Post (which represents a WooCommerce order) Object so you can use the same method for retrieving an order by single or multiple meta_data described in this section for using WP_Query.

Getting Orders by Meta Data with RAW SQL:

Get WooCommerce Orders by Meta Value:

global $wpdb;
$results = $wpdb->get_results( "
        SELECT pm.meta_value AS user_id, pm.post_id AS order_id
        FROM " . $wpdb->prefix . "postmeta AS pm
        LEFT JOIN " .$wpdb->prefix . "posts AS p
        ON pm.post_id = p.ID
        WHERE p.post_type = 'shop_order'
        AND pm.meta_key = 'customMeta'
        AND pm.meta_value = 'someValue'
        ORDER BY pm.meta_value ASC, pm.post_id DESC",
        OBJECT );

foreach($results as $result){
        echo $result->post_title; 
}

Get WooCommerce Orders by multiple Meta Value:

global $wpdb;
$results = $wpdb->get_results( "
        SELECT pm.meta_value AS user_id, pm.post_id AS order_id
        FROM " . $wpdb->prefix . "postmeta AS pm
        LEFT JOIN " .$wpdb->prefix . "posts AS p
        ON pm.post_id = p.ID
        WHERE p.post_type = 'shop_order'
        AND pm.meta_key = 'customMeta1'
        AND pm.meta_value = 'someValue1'
        AND pm.meta_key = 'customMeta2'
        AND pm.meta_value = 'someValue2'
        ORDER BY pm.meta_value ASC, pm.post_id DESC",
        OBJECT );

foreach($results as $result){
        echo $result->post_title; 
}

Get WooCommerce Orders by Meta Value and date:

PROTIP: You can get query various Order related dates, those being,

  • date_created,
  • date_modified,
  • date_completed,
  • date_paid

You can also various kinds of data base on these dates, for example,

  • Get orders from a specific date
  • Get orders greater than a specific date
  • Get orders greater than equal to a specific date
  • Get orders less than the a specific date
  • Get orders between a set of specific dates.

You can also use the above queries with either Timestamp or Standard Format.

Change the below code to have the date data you are interested in and or the specific query.

global $wpdb;
$results = $wpdb->get_results( "
        SELECT pm.meta_value AS user_id, pm.post_id AS order_id
        FROM " . $wpdb->prefix . "postmeta AS pm
        LEFT JOIN " .$wpdb->prefix . "posts AS p
        ON pm.post_id = p.ID
        WHERE p.post_type = 'shop_order'
        AND p.date_created = '2016-02-12'
        AND pm.meta_key = 'customMeta'
        AND pm.meta_value = 'someValue'
        ORDER BY pm.meta_value ASC, pm.post_id DESC",
        OBJECT );

foreach($results as $result){
        echo $result->post_title; 
}

Get WooCommerce Orders by ACF Field:

This is an easy one. ACF Fields save as ‘meta_data’ on a Post (which represents a WooCommerce order) Object so you can use the same method for retrieving an order by single or multiple meta_data described above.

Learn to customize WooCommerce Fully with an online Udemy course

If you’d like to know more about how to customize WooCommerce completely check out these courses from Udemy which are low cost, online and have been completed by thousands of people.

Related Posts

Last updated on January 14, 2023

Comments

No comments yet. Why don’t you start the discussion?

Leave a Reply

Your email address will not be published. Required fields are marked *