2

How to find all customers who ordered a certain SKU ?

1

The simplest way to do that, is to run a SQL statement. For example with the following statement you get email, firstname and lastname for all customer who have ordered a product with the specified sku:

select distinct(o.customer_email), o.customer_firstname, o.customer_lastname 
from sales_order o 
join sales_order_item oi on o.entity_id = oi.order_id 
where oi.sku = '[YOUR SKU HERE]';
0

Execute below script.

use Magento\Framework\App\Bootstrap;
require 'app/bootstrap.php';
$bootstrap = Bootstrap::create(BP,$_SERVER);

$objectManager = $bootstrap->getObjectManager();
$state = $objectManager->get('\Magento\Framework\App\State');
$state->setAreaCode('frontend');

$storeManager = $objectManager->get("\Magento\Store\Model\StoreManagerInterface");
$storeId = $storeManager->getStore()->getId();
$websiteId = $storeManager->getStore($storeId)->getWebsiteId();
$resource = $objectManager->get('Magento\Framework\App\ResourceConnection');
$connection = $resource->getConnection();
$sku = '24-WB04';

$Itemcollection = $objectManager->create('Magento\Sales\Model\Order\Item')->getCollection();
$Itemcollection->getSelect()->join('sales_order as order',"main_table.order_id = order.entity_id",array('order.customer_email'))->where("main_table.sku = '".$sku."'")->group(array('order.customer_email'));
print_r($Itemcollection->getData());
  • where can I place this code? Can you please give instructions? – DevAnd Mar 19 at 15:56
  • Can you share your code for where you want to show a list of the result (show customer by sku)? you can create a grid in admin using uiComponent and add dependency of class "Magento\Sales\Model\Order\Item'? – Amitkumar solanki Mar 19 at 17:51
0

To get all the customer with all ordered SKU use the following:-

SELECT o.customer_email,o.customer_firstname,o.customer_lastname,oi.qty_ordered 
AS numOfTimes FROM sales_order AS o
LEFT JOIN
sales_order_item AS oi ON o.entity_id=oi.order_id

For the specific SKU try the below:-

SELECT o.customer_email,o.customer_firstname,o.customer_lastname,oi.qty_ordered 
AS numOfTimes FROM sales_order AS o
LEFT JOIN
sales_order_item AS oi ON o.entity_id=oi.order_id WHERE oi.sku="YOUR_SKU"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.