Asked  8 Months ago    Answers:  5   Viewed   55 times

How can I get an array with Order IDs by Product ID?

I mean receive all orders where specific product is presented.

I know how to do this by MySQL, but is there a way to do this by WP_Query function?

 Answers

71

Updates:

  • 2017 - SQL query changed to "SELECT DISTINCT" instead of "SELECT" to avoid duplicated Order IDs in the array (then no need of array_unique() to filter duplicates…).

  • 2019 - Enabled product variation type support in the SQL Query

Then you can embed this in a custom function with $product_id as argument.
You will have to set inside it, the order statuses that you are targeting.

So here is the function that will do the job:

function get_orders_ids_by_product_id( $product_id ) {
    global $wpdb;
    
    // Define HERE the orders status to include in  <==  <==  <==  <==  <==  <==  <==
    $orders_statuses = "'wc-completed', 'wc-processing', 'wc-on-hold'";

    # Get All defined statuses Orders IDs for a defined product ID (or variation ID)
    return $wpdb->get_col( "
        SELECT DISTINCT woi.order_id
        FROM {$wpdb->prefix}woocommerce_order_itemmeta as woim, 
             {$wpdb->prefix}woocommerce_order_items as woi, 
             {$wpdb->prefix}posts as p
        WHERE  woi.order_item_id = woim.order_item_id
        AND woi.order_id = p.ID
        AND p.post_status IN ( $orders_statuses )
        AND woim.meta_key IN ( '_product_id', '_variation_id' )
        AND woim.meta_value LIKE '$product_id'
        ORDER BY woi.order_item_id DESC"
    );
}

This code goes in any php file.

This code is tested and works for WooCommerce version 2.5+, 2.6+ and 3+


USAGE EXAMPLES:

## This will display all orders containing this product ID in a coma separated string ##

// A defined product ID: 40
$product_id = 40;

// We get all the Orders for the given product ID in an arrray
$orders_ids_array = get_orders_ids_by_product_id( $product_id );

// We display the orders in a coma separated list
echo '<p>' . implode( ', ', $orders_ids_array ) . '</p>';
Wednesday, March 31, 2021
 
Tucker
answered 8 Months ago
100

To get all ancestors of a product category, you can use the Wordpress get_ancestors() function

The following custom shortcode function will output for each product category of a given product, the ancestors with the product category in a string as defined in your question:

add_shortcode( 'product_cat_list', 'list_product_categories' )
function list_product_categories( $atts ){
    $atts = shortcode_atts( array(
        'id' => get_the_id(),
    ), $atts, 'product_cat_list' );

    $output    = []; // Initialising
    $taxonomy  = 'product_cat'; // Taxonomy for product category

    // Get the product categories terms ids in the product:
    $terms_ids = wp_get_post_terms( $atts['id'], $taxonomy, array('fields' => 'ids') );

    // Loop though terms ids (product categories)
    foreach( $terms_ids as $term_id ) {
        $term_names = []; // Initialising category array

        // Loop through product category ancestors
        foreach( get_ancestors( $term_id, $taxonomy ) as $ancestor_id ){
            // Add the ancestors term names to the category array
            $term_names[] = get_term( $ancestor_id, $taxonomy )->name;
        }
        // Add the product category term name to the category array
        $term_names[] = get_term( $term_id, $taxonomy )->name;

        // Add the formatted ancestors with the product category to main array
        $output[] = implode(' > ', $term_names);
    }
    // Output the formatted product categories with their ancestors
    return '"' . implode('" | "', $output) . '"';
}

Code goes in function.php file of your active child theme (active theme). Tested and works.


USAGE:

1) In the php code of product page:

echo do_shortcode( "[product_cat_list]" );

2) In php code with a given product ID (here the product ID is 37 for example):

echo do_shortcode( "[product_cat_list id='37']" );

I think that the product name is not needed in your output as it is repetitive (on each product category). So you will get something like this:

"Cat1" | "Cat2>subcat1" | "Cat3>subcat1>subcat2"
Wednesday, March 31, 2021
 
tadman
answered 8 Months ago
60

To get the children variation Ids for a variable product, use WC_product get_children() method (that doesn't have/allow any arguments):

// (if needed) Get an instance of the WC_product object (from a dynamic product ID)
$product = wc_get_product($product_id);

// Get children product variation IDs in an array
$children_ids = $product->get_children();

// Get the first ID value
$children_id = reset($children_ids); 
// or 
$children_id = $children_ids[0];

Tested and works.

Friday, May 28, 2021
 
Asnexplore
answered 5 Months ago
55
$order = new WC_Order($order_id);

foreach ($order->get_items() as $item)
{
    $product_description = get_post($item['product_id'])->post_content; // I used wordpress built-in functions to get the product object 
}
Saturday, May 29, 2021
 
Markol
answered 5 Months ago
71

It's possible to get the total items count bought by the current customer in the past 30 days.

Here is the code of this function based on this answer:

function current_customer_month_count( $user_id=null ) {
    if ( empty($user_id) ){
        $user_id = get_current_user_id();
    }
    // Date calculations to limit the query
    $today_year = date( 'Y' );
    $today_month = date( 'm' );
    $day = date( 'd' );
    if ($today_month == '01') {
        $month = '12';
        $year = $today_year - 1;
    } else{
        $month = $today_month - 1;
        $month = sprintf("%02d", $month);
        $year = $today_year - 1;
    }

    // ORDERS FOR LAST 30 DAYS (Time calculations)
    $now = strtotime('now');
    // Set the gap time (here 30 days)
    $gap_days = 30;
    $gap_days_in_seconds = 60*60*24*$gap_days;
    $gap_time = $now - $gap_days_in_seconds;

    // The query arguments
    $args = array(
        // WC orders post type
        'post_type'   => 'shop_order',
        // Only orders with status "completed" (others common status: 'wc-on-hold' or 'wc-processing')
        'post_status' => 'wc-completed', 
        // all posts
        'numberposts' => -1,
        // for current user id
        'meta_key'    => '_customer_user',
        'meta_value'  => $user_id,
        'date_query' => array(
            //orders published on last 30 days
            'relation' => 'OR',
            array(
                'year' => $today_year,
                'month' => $today_month,
            ),
            array(
                'year' => $year,
                'month' => $month,
            ),
        ),
    );

    // Get all customer orders
    $customer_orders = get_posts( $args );
    $count = 0;
    if (!empty($customer_orders)) {
        $customer_orders_date = array();
        // Going through each current customer orders
        foreach ( $customer_orders as $customer_order ){
            // Conveting order dates in seconds
            $customer_order_date = strtotime($customer_order->post_date);
            // Only past 30 days orders
            if ( $customer_order_date > $gap_time ) {
                $customer_order_date;
                $order = new WC_Order( $customer_order->ID );
                $order_items = $order->get_items();
                // Going through each current customer items in the order
                foreach ( $order_items as $order_item ){
                    $count++;
                }
            }
        }
        return $count;
    }
}

This code goes in function.php file of your active child theme (or theme) or also in any plugin file.

The function accept an optional user_id argument (if needed). For example for a user_id with 56 value, you will use the function, this way:

// For user ID: "56".
$number_of_items_in_last_month = current_customer_month_count('56');

The function will get the current user ID in $user_id = get_current_user_id();, if you dont set an argument user_id value, this way:

// For current logged user.
$number_of_items_in_last_month = current_customer_month_count();

You can use this function in a conditional if statement to hide or replace the add-to-cart button through somme WooCommerce hooks or related templates.
You could get helped in that task, asking a new question including this code, and providing more details about how you want to do it.

This code is tested and works.


References: Checking if customer has already bought something in WooCommerce

Saturday, May 29, 2021
 
muffe
answered 5 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share