This is a child issue of #998080: Pre-calculate dynamic prices.

My original approach was to alter support onto the query via the Views hook, but I realized that what's really needed is a pair of Views sort / filter handlers for the Price field. I suppose we're going to need more meta information about the prices stored in commerce_calculated_price, so we need to introduce an info hook that lets modules define how exactly they're using the system. These handlers can then let the user specify which relationship should be required in joining to the table.

Comments

Has there been any movement or progress on this? Is this still relevant? What needs to be done here to allow/achieve this?

Nothing has moved forward here, so getting some eyes on it would be good. The gist of it is we need to ensure Price Pre-calculation is functioning properly and then provide a relationship from product table to the calculated price table so the pre-calculated price amounts can be used in the View for sorting / filtering / display. It shouldn't be a fundamentally difficult concept - in fact, I assumed it was going to be more difficult originally, but I don't see why a simple relationship can't work. The only thing we need to do is ensure the join is based on the key generated for matching Rules based on the current conditions at the time the View is rendered.

Status:Active» Needs work
StatusFileSize
new2.28 KB

Hopefully I can get the ball rolling on this. I'm fairly new to Commerce and brand new to Views handlers and hook_views_data() so feedback very welcome! I've attached a patch that adds an extra field, Precalculated price, based on a join with {commerce_calculated_price}. It uses a custom join handler so it can execute commerce_product_pre_calculation_key() to get the module_key value to join on. This all seems to work (note I haven't included a filter or argument handler for now).

Q1: Is this the right idea?

In my case, I've made a very simple feature, Commerce Sale Price, that allows a sale price to be set per product. The feature contains a checkbox and price field, and a rule. The rule checks for the existence of the fields, and if the checkbox is ticked it uses the sale price as the new price. The reason I first started looking at this issue was so I can sort by the actual price, whether it be on sale or not. Unfortunately when commerce_product_pre_calculation_key() checks which rules are active, the sale price rule is ignored (presumably because there isn't a product with the necessary fields, so the condition doesn't evaluate as true).

Q2: Have I gone down the wrong road with how I've done Commerce Sale Price?

As a solution to the problem above, I wanted to allow a manual override of the key used on the join. I can't find a way to add options to the join itself, so the best I could do was to create two entries in hook_views_data for {commerce_calculated_price} (by using table aliases): one defines the field and a join handler that reads from the field's options; the other defines the sort and a join handler that reads from the sort's options. I've included the hook_views_data() below in case it's unclear. It's very ugly but it works!.

Q3: Is there a way to add options to a join itself? Is there another, better way to do this?

TIA!

/**
* Implements hook_views_data().
*/
function commerce_price_views_data() {
  $title = t("Precalculated price");
  $description = t("The price after applying price calculation rules");
  // Add the precalculated price table with an aliased table name for use (only)
  // with the field.
  $data['commerce_calculated_price_field'] = array(
    'table' => array(
      'group' => t("Commerce Product"),
      'join' => array(
        'commerce_product' => array(
          'table' => 'commerce_calculated_price',
          'left_field' => 'product_id',
          'field' => 'entity_id',
          'handler' => 'commerce_product_pricing_join_field',
        ),
      ),
    ),
  );
  $data['commerce_calculated_price_field']['amount'] = array(
    'title' => $title,
    'help' => $description,
  );
  $data['commerce_calculated_price_field']['amount']['field'] = array(
    'handler' => 'commerce_product_pricing_field_price_handler',
    'click sortable' => TRUE,
  );
  // Add the precalculated price table with an aliased table name for use (only)
  // with the sort.
  $data['commerce_calculated_price_sort'] = array(
    'table' => array(
      'group' => t("Commerce Product"),
      'join' => array(
        'commerce_product' => array(
          'left_field' => 'product_id',
          'field' => 'entity_id',
          'handler' => 'commerce_product_pricing_join_sort',
          'table' => 'commerce_calculated_price',
        ),
      ),
    ),
  );
  $data['commerce_calculated_price_sort']['amount'] = array(
    'title' => t("Precalculated price"),
    'help' => t("The price after applying price calculation rules"),
  );
  $data['commerce_calculated_price_sort']['amount']['sort'] = array(
    'handler' => 'commerce_product_pricing_sort_price_handler',
    'click sortable' => TRUE,
  );
  return $data;
}

OK I hadn't found the documentation on precalculated prices (sorry!). Looking through that it seems the correct way to do things with Commerce Sale Price is to use a Rules Component. This enables its effects to be included in the 'normal' precalculated price (where module_key is the empty string). Hopefully that means the patch should work ok as is, will report back.

Yep, seems to be working for me. I've cleaned it up slightly. A few points:

  1. I don't know if what I've done here is what Ryan was going to do. It did occur to me that it would be nicer from a UX point of view not to have a separate field for the precalculated price, but just have an option to use the precalculated data with normal price fields. Could you use a custom field, sort, argument and filter handler for price fields that joined with the right table based on how the user configured the field? I don't know how much work that would be and I think it's beyond my ken :) This would also allow the normal choice of formatters (not possible with the patch)
  2. I wasn't sure if I had to handle the language or not (can price fields be translatable?). Currently no constraints are put on that column.
  3. Similarly, I wasn't sure if the price field could ever have a cardinality other than 1. Again I've left out any mention of that column.
  4. It only exposes the field commerce_price.
  5. It doesn't have argument or filter handlers.
  6. It doesn't expose the currency code or data.
  7. It doesn't offer a choice of formatters.

Hope this helps!

In case anyone's interested I've made a little sandbox module that has a rule that updates a product's precalculated price after it's saved (only for use until something more permanent can be arranged!). That actually covers all of my needs (for now...).

After applying the patch I can see the commerce_calculated_price field but when I try to add it to my view, I get a message:

"The handler for this item is broken or missing and cannot be used. If a module provided the handler and was disabled, re-enabling the module may restore it. Otherwise, you should probably delete this item."

Any suggestions if this is something I can resolve, or is it a problem with the patch?

Also I noticed that the calculated price field doesnt seem to appear in the list of fields that are indexed in Search API, if one needs to create an indexed display of products.

rszrama, are there any new solutions relatively this issue? :) Thanks!

Nope, nothing new really. It still just remains to be done. : )

It would be cool if this issue would have been resolved :) It is very necessary function!

After applying the patch I can see the commerce_calculated_price field but when I try to add it to my view, I get a message:

I believe you got errors while applying patch, i suggest that this lines from patch was ignored, in your case (and in my one too):

+files[] = includes/views/handlers/commerce_price_handler_field_calculated_price.inc
files[] = includes/views/handlers/commerce_price_handler_filter_commerce_price_amount.inc
+files[] = includes/views/handlers/commerce_price_handler_join_calculated_price.inc

Just re-roll patch or add manualy this lines to .info file + clear cache would solve this issue.

Hi,

Any progress for #7 please? Would be great if this could be solved!
Greetings, Martijn

any update about how to use calculated price as filter?

Same Here. I'm looking to create a view that display only discounted products (created with commerce discount module). It seems it's not easy to do that using views actually because there are some essentials filters missing.

subscribing. I tried the patch, but it did not work (it actually broke my cart) - however, I would like to know of a way to display the price pre-calculation

Issue summary:View changes

Hallo. I think, help user on ecommerce site to sort by sale price (after appling discount) is really need. Every customer compare by price.
Please help me how you solve this in your case. Or on your commerce site you dont use this? I googled whole day and cant find working sollution.
I use drupal commerce 1.8.

Thanks for your help.

@AndyF

I think we would want to bring your module into commerce/price. And we would probably need some method to extend it for discount modules, so the rule can be ran every time those are updated?

Nothing important.