Add Views fields that count products sold and a user's orders

Working on that.

Not working on that.

I think we are supporting this out of the box using views grouping feature of Views 3.

For getting the number of orders of a given user:
Create a "Commerce order" view and add a relationship for Order: owner
Enable Grouping
Add user data, select Group results together
Add order id field, select Count.

For getting the number of times that a product has been sold.
Create a "Commerce order" view and add a relationship for Order: Line items and then another for Line items product
Enable Grouping
Add product data, select Group results together
Add line item quantity field, select Sum
Add a filter for the order state, probably "Completed"

This looks feasible on Commerce Orders views, but not on Product/Node:Product display ones.

It seems something is missing, like the relationship between line items and orders (#1035006: Create entity properties for orders in line items) and between products and line_items (couldn't find an issue for that).

Ryan, what's the plan on this one?
The issue is old, I'm guessing now everything is possible through Views, the question is what do we want to do.

Subscribe... Have to use a PHP filter to do a Top Sales Views for Anonymous users #1276900: Top Sales Views for Anonymous users without duplicates. The view must be a content or Commerce Product view instead of a Commerce Order Views to get rid of duplicates via aggregation and we cant reach the orders via relationships from content or Commerce Product.

I don't see that working in a generic way in core. Any taker for a contrib?

Done!
git clone http://git.drupal.org/sandbox/johnkennedy/1295970.git commerce_sales
Well the products sold part anyway.
I would love for a couple of co-maintainers to come on-board to help me expand this out.

John

See previous.

Can we use http://drupal.org/project/views_summarize for this?

I'd really love to be able to sort a view or product displays by number sold, showing the five products most sold, or sorting the entire catalog with the more popular sellers at the top. Is there any progress on this?

You can have a look at commerce_reports 3goose

Hi, could this view help with this thread: http://drupal.org/node/1292104#comment-6866420
Greetings, Martijn

I need to get a list of products with their purchase counts. If no one has purchased the product yet it needs to say 0. Anything that starts from the orders table will fail this. I need to be able to make a reference from the products -> line items -> orders. Would love any advice or direction :)

None of the solutions above seem to be working for me:

Building views as mentioned in #3 will exclude any product that hasn't been ordered, instead of adding a "Product -- 0" item to the report.

The views_summary solution mentioned in #10 would also need to be applied to a view. A view of orders will exclude any product that hasn't been ordered, and products suffer from the lack of relationships mentioned in this comment.

The commerce sales module mentioned in #8 keeps track of product sales separately from actual orders. This is a very bad idea. We already have the data, duplicating it via rules is just going to introduce irregularities.

I looked a bit at the database structure to write my own views relationships in the correct direction, but it looks like the IDs I need access to are stored inside serialized arrays in both the commerce_line_item and commerce_order tables. Can that be right? Why is there no product_id field on the commerce_line_item table?!

I'm not sure there is a way to build the relationship we need if this is the data architecture we're starting with.

Changing status. Also, if this basic functionality is missing ("give me a report of all products, with how many people purchased each one") I think this is a bug.

Hello,

I needed the same thing as is being asked for by other in this thread. I have a view of product displays, with pages and blocks of various order/filter combos. I wanted a page that showed best sellers. As already pointed out, there is no way at present to use Relationships to link from a product reference field on a node to line items to orders.

I found a solution for my use case. Warning: this is very specific to this specific website, this is not a copy paste solution, and also, I feel a little dirty and need a shower now :P. As an overview, I setup the page in views as far as I could with the Content: Referenced products relationship. I then implemented hook_views_post_build() in my module and changed the query from there like so:

/**
 * Implements hook_views_post_build().
 */
function mymodule_views_post_build(&$view) {

  if ($view->name == 'artwork_lists') {

    if (in_array($view->current_display, array('best_sellers'))) {
      $view->build_info['query']->addExpression('COUNT(nid)', 'number_sold');

      // In the view settings, we configured a relationship to join on the product reference field.  This gets us to the commerce_product
      // table.  We want to continue on to the:
      // 1. Line item - gives us products that have been ordered, also gives a total (through the commerce_total field on line items) to base a revenue calculation on
      // 2. Order - so we are able to limit by order status.
      $view->build_info['query']->innerJoin('field_data_commerce_product', 'field_data_commerce_product', 'commerce_product_field_data_field_artwork_print.product_id = field_data_commerce_product.commerce_product_product_id AND (field_data_commerce_product.entity_type = \'commerce_line_item\' AND field_data_commerce_product.deleted = \'0\')');
      $view->build_info['query']->innerJoin('commerce_line_item', 'commerce_line_item', 'field_data_commerce_product.entity_id = commerce_line_item.line_item_id');
      $view->build_info['query']->innerJoin('commerce_order', 'commerce_order', 'commerce_line_item.order_id = commerce_order.order_id');
      $view->build_info['query']->leftJoin('field_data_commerce_total', 'field_data_commerce_total', 'commerce_line_item.line_item_id = field_data_commerce_total.entity_id AND (field_data_commerce_total.entity_type = \'commerce_line_item\' AND field_data_commerce_total.deleted = \'0\')');

      $view->build_info['query']->condition('commerce_line_item.type', 'print');
      $view->build_info['query']->condition('commerce_order.status', array('pending', 'processing', 'completed'));

      $view->build_info['query']->groupBy('nid');

      $view->build_info['query']->orderBy('number_sold', 'DESC');
    }
  }
}

Hope that helps someone else, or maybe helps lead to a more permanent solution from someone who knows these things better than I do :).

Thanks,
Matt

So here's a crazy work around (it's crazy, but doesn't feel as dirty as the above).

1) Create a bestsellers view on the orders table, something like this one.
...but you'll need to make a few changes:
1a) add an argument of Product ID
1b) add an "empty text" field that shows 0
1c) remove (or hide) all fields that are not the count
1d) optionally also remove the sort (we'll only be showing 1 row at a time, so that's not going to be necessary anymore.)

2) Install the views_field_view module

3) Create a view of your commerce products (or of your commerce product display nodes)
3a) you'll need to have a product ID field on this view, but you can hide it from display.
3b) add a view field. Choose your bestsellers view, and pass it the product ID as an argument.

Voila!

@rszrama I can see that the line item has an entity reference to the product, but unfortunately this listing has to start FROM the product and go TO the line item. The reference needs to go the opposite way. Like wise, we need to go FROM the line item TO the order, but the reference there goes the other way there, too. The relationships for commerce are built starting from the orders and going backwards Order->line_item->product but if you want a list of your products with a status of weather they've been sold or not, you need the references to go the other way: Product->line_item->order. The values I need to make queries for those relationships are saved in serialized arrays in both the line item table (need: product id), and the order table (need: line_item) so there's nothing in the database tables to query. That's probably why they were excluded initially ;)

The views view solution works, by leveraging the order->line_item->product relationship chain to show a list of products *sold*. Then from another, separate list of *all* products, we ask for a single product's result from the first list (if it exists) or zero (if it doesn't).

Hello Guys,

I want something similar. I have a marketplace website where various sellers sell their products. I want each seller to sell up to certain limit. When he reached his sales limit, then the end users will not be able to purchase his products.

How can we accomplish this. Is there any way using rules or views or we have to write some custom coding for this.

I shall be thankful

If you have a custom entity for each seller, you could have some type of integer field.

When an order is placed, you could use rules to loop through line items, find the owner of the product (maybe use a entity reference field or the author of the item) and then add a count back to that original field on the seller.

You would probably need to also setup something on each product, that does something similar to the stock system, but instead of checking the stock field, you check the authors/sellers integer field. When it hits a limit, you hide the add to cart button.

---
This seems a little off topic for a best sellers conversation.

Was having a difficult time getting number of each product that had been sold.

pcambra's suggestion [from 6 years ago!] worked just fine.

Thanks!

Wyckham

There is Commerce Reports. When doing a count you need to aggregate the orders table and do a count on the results.