Get all Orders IDs from a product ID in Woocommerce

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>';

I have made some changes in your SQL and set the code in an utility function:

function get_active_members_for_membership($memberships){
    global $wpdb;

    // Getting all User IDs and data for a membership plan
    return $wpdb->get_results( "
        SELECT DISTINCT um.user_id, u.user_email, u.display_name, p2.post_title, p2.post_type
        FROM {$wpdb->prefix}posts AS p
        LEFT JOIN {$wpdb->prefix}posts AS p2 ON p2.ID = p.post_parent
        LEFT JOIN {$wpdb->prefix}users AS u ON u.id = p.post_author
        LEFT JOIN {$wpdb->prefix}usermeta AS um ON u.id = um.user_id
        WHERE p.post_type = 'wc_user_membership'
        AND p.post_status IN ('wcm-active')
        AND p2.post_type = 'wc_membership_plan'
        AND p2.post_title LIKE '$memberships'
    ");
}

This code goes on function.php file of your active child theme (or theme). Tested and works.

USAGE:

// Replace 'My Membership' by your targeted membership name.
$users_data = get_active_members_for_membership('My Membership');

// Test raw output
echo '<pre>'; print_r( $users_data ); echo '</pre>';

I have revisited the original code answer a bit and I have enabled the display of those parent products linked names on orders and email notifications:

// Adding the grouped product ID custom hidden field data in Cart object
add_filter( 'woocommerce_add_cart_item_data', 'save_custom_fields_data_to_cart', 20, 2 );
function save_custom_fields_data_to_cart( $cart_item_data, $product_id ) {
    if( ! empty($_REQUEST['add-to-cart']) && $product_id != $_REQUEST['add-to-cart']
    && is_numeric($_REQUEST['add-to-cart']) ){
        $group_prod = wc_get_product($_REQUEST['add-to-cart']);
        if ( ! $group_prod->is_type( 'grouped' ) )
            return $cart_item_data; // Exit

        $cart_item_data['grouped_product'] = array(
            'id' => $_REQUEST['add-to-cart'],
            'name' => $group_prod->get_name(),
            'link' => $group_prod->get_permalink(),
            'visible' => $group_prod->is_visible(),
        );

        // Below statement make sure every add to cart action as unique line item
        $cart_item_data['grouped_product']['unique_key'] = md5( microtime().rand() );
    }
    return $cart_item_data;
}


// Add the parent grouped product name to cart items names
add_filter( 'woocommerce_cart_item_name', 'custom_product_title_name', 20, 3 );
function custom_product_title_name( $cart_item_name, $cart_item, $cart_item_key ){
    // The product object from cart item
    $product = $cart_item['data'];
    $product_permalink = $product->is_visible() ? $product->get_permalink( $cart_item ) : '';

    // The parent product name and data
    if( isset( $cart_item['grouped_product'] ) ){
        $group_product = $cart_item['grouped_product'];
        $group_prod_link = $product->is_visible() && is_cart() ? $group_product['link'] : '';

        if ( ! $group_prod_link )
            return $group_product['name'] . ' > ' . $product->get_name();
        else
            return sprintf(
                '<a href="%s">%s</a> > <a href="%s">%s</a>',
                esc_url( $group_prod_link ),
                $group_product['name'],
                esc_url( $product_permalink ),
                $product->get_name()
            );
    }
    else
        return $cart_item_name;
}

// Save grouped product data in order item meta
add_action( 'woocommerce_checkout_create_order_line_item', 'added_grouped_order_item_meta', 20, 4 );
function added_grouped_order_item_meta( $item, $cart_item_key, $values, $order ) {
    if( isset($values['grouped_product']) ){
        $item_id = $item->get_id();
        $grouped_data = $values['grouped_product'];
        unset($grouped_data['unique_key']);
        $item->update_meta_data( '_grouped_product', $grouped_data );
    }
}

// Display grouped product linked names in order items (+ email notifications)
add_filter( 'woocommerce_order_item_name', 'custom_order_item_name', 20, 3 );
function custom_order_item_name( $item_name, $item, $is_visible ) {
    $product = $item->get_product();
    $product_id = $item->get_product_id();
    $product_permalink = $is_visible ? $product->get_permalink( $item ) : '';
    $grouped_data = wc_get_order_item_meta( $item->get_id(), '_grouped_product', true );
    if( empty($grouped_data) ){
        $item_name = $product_permalink ? sprintf(
            '<a href="%s">%s</a>',
            esc_url( $product_permalink),
            $item->get_name()
        ) : $item->get_name();
    } else {
        $item_name = $product_permalink ? sprintf(
            '<a href="%s">%s</a> > <a href="%s">%s</a>',
            $grouped_data['link'],
            $grouped_data['name'],
            esc_url( $product_permalink) ,
            $item->get_name()
        ) : $grouped_data['name'] . ' > ' . $item->get_name();
    }
    return $item_name;
}

// Display on backend order edit pages
add_action( 'woocommerce_before_order_itemmeta', 'backend_order_item_name_grouped', 20, 3 );
function backend_order_item_name_grouped( $item_id, $item, $product ){
    if( ! ( is_admin() && $item->is_type('line_item') ) ) return;

    $grouped_data = wc_get_order_item_meta( $item_id, '_grouped_product', true );
    if( empty($grouped_data) ) return;
    $product_link = admin_url( 'post.php?post=' . $grouped_data['id'] . '&action=edit' );
    $grouped_name_html = '<a href="' . esc_url( $grouped_data['link'] ) . '" class="wc-order-item-name">' . esc_html( $grouped_data['name'] ) . '</a>';
    echo '<br><br><div class="wc-order-item-name">
        <small><strong>'.__('Grouped parent').':</strong></small><br>
        ' . $grouped_name_html . '
    </div>';
}

Code goes in function.php file of your active child theme (or theme);

Tested and works.