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.

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

cdale’s picture

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

rszrama’s picture

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.

AndyF’s picture

Status: Active » Needs work
FileSize
2.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;
}
AndyF’s picture

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.

AndyF’s picture

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!

AndyF’s picture

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...).

pmackay’s picture

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.

CSoft’s picture

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

rszrama’s picture

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

CSoft’s picture

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

vlad.dancer’s picture

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.

Summit’s picture

Hi,

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

dgastudio’s picture

any update about how to use calculated price as filter?

zmove’s picture

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.

MaWebDesigns’s picture

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

voloda86’s picture

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.

dubcanada’s picture

@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?

zmove’s picture

Nothing important.

nithinkolekar’s picture

Is it required that, patch @ #8 should be applied to show price format when " Content: Rendered Node" is added to views with display : "show complete entity" and view mode: "product list" ?

Just like Commerce kickstart, new "Node:product list" view mode is created and activated with price field's format is set to "Formatted amount with savings".
I think this is a hackish way to get calculated price into view if it is possible? or "sale price" field itself is not accessible by view until patch is applied.

mvdve’s picture

Patch #5 from AndyF works great on normal products. But I can't get it to work with indexed products (with search API).
I tried with the commerce_price field and also created an indexed relationship amount field in search_api.

This would be a great feature for large shops where the products are displayed in a big table.

czigor’s picture

johnpicozzi’s picture

Does this Patch Still Apply... Tried it out and it didn't seem to work.

wouhn’s picture

subscribing

mvdve’s picture

@wouhn. Please use the subscribe button below the issue information block.

pinkonomy’s picture

I am also interested in using this with Seach api views... any progress?
I think this is very needed as most commerce sites use search api (and of course Kickstart indexes nodes).
Its a pitty that in 2015 this is not solved :(

rollingnet’s picture

I think this patch should be included in Drupal commerce ASAP, since price calculation is a core business.
It's not possibile for a serious e-commerce platform to live without a basic function as (calculated) price sorting.
Search api support should be included, too. Quite any medium-to-large Drupal Commerce site uses it.
I can give a small contribution in terms of programming, since I don't know price calculation functions in Drupal Commerce very deeply, but I can contribute by testing the code.
Every Drupal Commerce lover gave a contribution, please.
This issue is of 2011: it's time to close it.

wqmeng’s picture

Is this patch still working? I now just use a dummy price for sort and then use rule to display the real sale price on the views page. Also I think this should be work out already.

minff’s picture

For anybody looking for a Views filter to go with patch in #5, here's the very simple way of achieving it. Just add the filter handler to the array in commerce_price_views_data():

      'amount' => array(
        'title' => t("Precalculated price: @field_name:amount", array('@field_name' => $field_name)),
        'sort' => array(
          'handler' => 'views_handler_sort',
          'click sortable' => TRUE,
        ),
        'filter' => array(
          'handler' => 'commerce_price_handler_filter_commerce_price_amount',
          'allow empty' => FALSE,
        ),
      ),
radamiel’s picture

https://www.drupal.org/project/commerce_pc_field - here is a module that solves problem of sorting prices that have been modified by Rules. This module allows to add Calculated-price field to Commerce Product entity and contain calculated price of product after updating entity or after clicking button at Store > Configuration > Product pricing rules > Pre-calculated field.
Having calculated-price field we can use it in Views in Sort Criteria section to sort products.

vacho’s picture

Patch of #5 works for me

I have also applied the sugestion on #11
and I have added the proposed code on #28

I think this patch should be applied to the module. It is something very basic that it works well.

torgosPizza’s picture

Status: Needs work » Needs review

If the patch works for you we should run it through Review again and to get it RTBC.

Status: Needs review » Needs work

The last submitted patch, 5: commerce-views_precalculated_price-1020050-5.patch, failed testing.

SocialNicheGuru’s picture

it doesn't apply to the latest dev

patch -p1 < commerce-views_precalculated_price-1020050-5.patch
patching file modules/price/commerce_price.info
Hunk #1 FAILED at 12.
1 out of 1 hunk FAILED -- saving rejects to file modules/price/commerce_price.info.rej
patching file modules/price/includes/views/commerce_price.views.inc
patching file modules/price/includes/views/handlers/commerce_price_handler_field_calculated_price.inc
patching file modules/price/includes/views/handlers/commerce_price_handler_join_calculated_price.inc

yuradoc’s picture

I made union of the proposed code with the same set as vacho listed.

AndyF’s picture

I thought I should mention that there hasn't really been a code review on the original patch AFAICT and there are some unaddressed concerns in #5 (maybe everything's fine, I just don't know!).

yuradoc’s picture

I use #34 patch that based on AndyF patch + some notices from discussion taken place here (like #30) - it works for me.

andyanderso’s picture

I am trying to get the calculated price to display on a veiws list of products. It works fine when viewing the product but it will not display the calculated price when looking at the list of products as a view. I tried modifying my commerce files with the patch in #34 but it still did not work. What am I missing?

NicklasMF’s picture

Is there a 8.x version for this issue?