How to check if a user has ever ordered a product/list of products

Question

Given a list of product IDs, I'm trying to determine if a user has ever purchased any of them.

At the moment my logic consists of loading all the orders related to a user, then looping through the order lines to check individual product IDs:

$ordered = FALSE;

$node_wrapper = entity_metadata_wrapper('node', $node);
$product_ids = array();
foreach ($node_wrapper->field_products as $product) {
  $product_ids[] = $product->product_id->value();
}

$orders = commerce_order_load_multiple(array(), array('uid' => $account->uid, 'status' => 'checkout_checkout'));
foreach ($orders as $order) {
  $wrapper = entity_metadata_wrapper('commerce_order', $order);
  foreach ($wrapper->commerce_line_items as $order_line) {
    $product = $order_line->commerce_product;
    if (in_array($product->product_id->value(), $product_ids)) {
      $ordered = TRUE;
    }
  }
}

This feels like a long way round, and that perhaps I'm missing a helper function somewhere in Commerce that would get me this information.

Anyone know if such a function exists, or if there's a more elegant method to get this data?

NB. I know 'checkout_checkout' isn't the right order status for the production code, this is just test code at the moment.

I'm looking for an API solution to this; building up an SQL query would obviously be very easy, but more verbose and error-prone. What I'm looking for is a more elegant solution than my existing one (if one exists; it might not).

Answer 1

Here's an alternative approach, using an SQL query

$ordered = FALSE;

$node_wrapper = entity_metadata_wrapper('node', $node);
$product_ids = array();
foreach ($node_wrapper->field_products as $product) {
  $product_ids[] = $product->product_id->value();
}

// Query order details for products
// Note this is the line item product reference table
$query = db_select('field_data_commerce_product', 'p');
// Example if you want more fields, but would need to change ->fetchCol()
// $query->fields('o', array('order_id', 'order_number', 'uid', 'mail'));
$query->fields('o', array('uid'));
$query->innerJoin('field_data_commerce_line_items', 'l', 'l.commerce_line_items_line_item_id = p.entity_id');
$query->innerJoin('commerce_order', 'o', 'o.order_id = l.entity_id');
$query->condition('p.commerce_product_product_id', $product_ids, 'IN');
$query->groupBy('o.order_id');
// Fetch array of uids
$result = $query->execute()->fetchCol();

// Check if account->uid in array of order uids
// If so, uid has ordered product.
if(in_array($account->uid, $result)){
  $ordered = TRUE;
}

Answer 2

In a project I added a hidden field called 'Bought history' to user account, containing a list of products a user has bought, then using Rules module in the event of "completing checkout" I added the product ID to user's bought history.

Now I easily load user data, and check if products are stored in this list or not (Even node ID can be saved rather than Product ID)