Get users who has bought this product (MAGENTO)

Is it possible in magento to filter user based on the products they have bought? For eg.

How can I get all the users who have bought product A from category B

mysql query like

SELECT users From table users, table products ..... WHERE user has purchased product A .

Please give some ideas, I needed to make this work. Thanks

-------------Problems Reply------------

If you want an actual query, you can probably do something as simple as (add additional joins to get customer information from EAV):

SELECT DISTINCT o.customer_id FROM sales_flat_order_item i
INNER JOIN sales_flat_order o ON o.entity_id = i.order_id
WHERE o.customer_id IS NOT NULL
AND i.sku = 'some-product-sku'

Using Magento models, this should work for you:

<?php

require_once 'app/Mage.php';

/*
* Initialize Magento. Older versions may require Mage::app() instead.
*/
Mage::init();

/**
* Get all unique order IDs for items with a particular SKU.
*/
$orderItems = Mage::getResourceModel('sales/order_item_collection')
->addFieldToFilter('sku', 'some-product-sku')
->toArray(array('order_id'));

$orderIds = array_unique(array_map(
function($orderItem) {
return $orderItem['order_id'];
},
$orderItems['items']
));

/**
* Now get all unique customers from the orders of these items.
*/
$orderCollection = Mage::getResourceModel('sales/order_collection')
->addFieldToFilter('entity_id', array('in' => $orderIds))
->addFieldToFilter('customer_id', array('neq' => 'NULL'));
$orderCollection->getSelect()->group('customer_id');

/**
* Now get a customer collection for those customers.
*/
$customerCollection = Mage::getModel('customer/customer')->getCollection()
->addFieldToFilter('entity_id', array('in' => $order->getColumnValues('customer_id')));

/**
* Traverse the customers like any other collection.
*/
foreach ($customerCollection as $customer) {
var_dump($customer->getData());
}

It's pretty ugly though (instantiates multiple models, executes a bunch of queries under the covers), you could probably write your own model to make this -a lot- prettier.

You have to base your query on orders. If you want to do it by SQL query, you have to it by the following table:

  • sales_flat_quote, sales_flat_order_item to get the link between customer and product
  • catalog_category_product to get the link between category and product
  • catalog_product_entity to get the product id in function of the sku
  • ...

Good luck

try these models

$orders = Mage::getModel('sales/order')->addAttributeToSelect('*')->getCollection();

$order_items = Mage::getResourceModel('sales/order_item_collection')
->addAttributeToSelect('sku')
->addAttributeToSelect('created_at')
->addAttributeToSelect('order_id')
->addAttributeToFilter('order_id', array('in' => $orders_ids))->load();

Category:magento Views:0 Time:2012-03-01
Tags: magento

Related post

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.141 (s). 11 q(s)