After much experimentation, I've found no easy way to generate useful total sales calculations in a Views table.

Each record in the {uc_order_product} table has a quantity and sell price field which can be used by Views. Total Sales should be generated by multiplying the two fields, putting the result in a Views column, and using Views SUM() aggregation on the column. However, Views aggregation is done before the math expression is calculated, so this doesn't work.

The total sale price field should be generated by multiplying the paid price by the quantity for each record. The SUM() aggregation should work on that. There should be similar generated fields for Ordered product total cost of goods and Ordered product total weight.

Ubercart's built-in reports ought to be generated with Views anyway, and this is a step in that direction.

Since I need this soon, I'm likely to jump in, learn Views programming and try to do this myself. Any tips or advice about whether this is a good idea? The other choices I can think of are a separate module that adds the fields via some sort of _alter(), or some module that lets you insert PHP to calculate field values. If it is a good idea, does anyone have any pointers on how to make a custom calculated Views field handler? I can't find any documentation for doing this in Views 3.

(Note: Order: Total cost can do some of this, but can't provide per-product filters or aggregation.)

Files: 
CommentFileSizeAuthor
#19 ubercart_add_ordered_product_views_fields-1876496-19.patch7.51 KBDanZ
PASSED: [[SimpleTest]]: [MySQL] 2,804 pass(es).
[ View ]
#18 ubercart_add_ordered_product_views_fields-1876496-18.patch7.51 KBDanZ
PASSED: [[SimpleTest]]: [MySQL] 2,796 pass(es).
[ View ]
#17 ubercart_add_ordered_product_views_fields-1876496-17.patch7.51 KBDanZ
PASSED: [[SimpleTest]]: [MySQL] 2,798 pass(es).
[ View ]
#15 ubercart_add_ordered_product_views_fields-1876496-14.patch7.35 KBDanZ
PASSED: [[SimpleTest]]: [MySQL] 2,798 pass(es).
[ View ]
#13 1876496-order_product_total_price-13.patch2.67 KBlongwave
PASSED: [[SimpleTest]]: [MySQL] 2,796 pass(es).
[ View ]
#10 order_product_total_price-1876496-10.patch4.69 KBDanZ
PASSED: [[SimpleTest]]: [MySQL] 2,798 pass(es).
[ View ]

Comments

There are projects like http://drupal.org/project/views_calc which may help here, have you looked at existing Views contrib?

Yes. I did a lot of research to figure this out. There is no clean way to do this from the Views API.

Views Calc has the same problem that the built-in Views math expression field has. It does its calculations after the aggregation, not before.

Attaching computed fields would work if Ordered Products are entities, but I don't think they are.

Adding new fields to the {uc_order_products} table would do the trick, but that would probably be overkill, and a waste of space. It would be efficient from Views perspective, though, as it could do aggregation and summing via DB queries.

Using Views PHP fields would work, but they would have to do all the queries and aggregation themselves, and the store admin would have to write and insert all the PHP himself, which is not reasonable for most admins. Also, a look at the issue queue makes it appear to be really buggy.

Upon further reflection, the best way to do this is to turn ordered products into fieldable entities. That would make it possible to attach calculated fields, and would let them work with Rules and Views nicely. It would also make it a lot easier for add-on modules to work with them.

However, I don't know if that would break anything. It would certainly be more involved. If entities are the right way to do this, it might be better to handle the immediate need with a separate module using an _alter(), and make the entity thing a separate request.

Any thoughts before I jump in?

Actually, there are a lot of things in Ubercart that would be better as entities (if they aren't already). Off the top of my head, some possibilities are ordered products, packages, shipments, orders, line items, payment receipts, payment methods, quote methods, and shipping methods. In addition to the nice Rules integration, Views integration, and CRUD stuff, making these fieldable would let us (eventually) get rid of that hideous "data" field.

I'll put this into the Drupal 8 issue, if it's not there already.

Ordered products are already entities - see uc_order_entity_info() - and although they are not fieldable at present, I don't see why we can't change that.

Oh, good. I see that orders are also entities.

Yes, I think making them fieldable is a good idea. That would allow the calculated fields method to work, and would open up possibilities for other add-on modules. There are instructions at http://drupal.org/node/474582.

I think it still might be a good idea to add the calculated fields in Ubercart core, so that someone setting up a View could do the total sales thing without installing an additional module, writing formulas, etc. I'll look into what this will take.

Ok, I've been researching this, and it's a lot hairier than I expected. I've got a lot of bits and pieces of information, but they don't yet add up to a path to doing this. Can anyone suggest the right direction to take here?

Here's what I've got so far on the Entity side:

Yes, Order Products are entities, as shown by uc_order_entity_info(). No, they are not fieldable.

There is something called Entity API. It appears to provide a toolbox for defining entities so the developer doesn't have to do everything. It also seems to make interfacing with other modules more straightforward.

The uc_order_product entity type defines a controller class and a metadata controller class as follows:

<?php
     
'controller class' => 'UcOrderProductController',
     
'metadata controller class' => 'UcOrderProductMetadataController',
?>

Class UcOrderProductController extends EntityAPIController. I think that means that Order Products entities use the Entity API. There is no entity class (I don't know if that's important).

UcOrderProductController::buildContent() does calculate a total field! However, I haven't found adequate documentation for what buildContent() is used for. It might be just for building a data structure to display somehow. The value certainly doesn't show up in Views.

Class UcOrderProductMetadataController extends EntityDefaultMetadataController. I don't see anything about Entity API, but I don't know if that's important.

UcOrderProductMetadataController::entityPropertyInfo() defines the names of the fields from the schema, and adds a node property. The node property shows up in Rules, but not in Views. The node property defines "getter" and "setter" callbacks.

It looks like total_price, total_cost, and total_weight properties could be defined here with callbacks to do the multiplication, right? The getter callback ought to be called automatically when that value is needed, right? I haven't found full docs on how to describe the computed fields here. Something else must still be done to get them to show up in Views.

Then there's entity.views.inc, including EntityDefaultViewsController. This appears to somehow magically provide Views integration for entities, but I haven't completely figured out how it works yet. There is some additional information about this that's worth reading. As far as I can tell, Ubercart doesn't use it. Is that true? Should it be used?

Here's what I've got on the Views side:

The Views fields are described in uc_order_views_data(). They appear to be only the fields straight out of the schema. There isn't anything relating the Views field descriptions here to the schema or entity field names. I'm thinking that it's just the index names on the arrays, such as:

<?php
  $data
['uc_order_products']['order_id'] = array(
...
  );
?>

What I'm thinking:

Add the three fields to the entity, with getter callbacks to do the multiplication by putting stuff in UcOrderProductMetadataController::entityPropertyInfo(). This should also make the information available to Rules, right?

Add the three fields to the View info in uc_order_views_data().

Think that would do it, or are there more dots to connect?

Ok, I'm partway there.

The sticking point now is that Views needs to access the new fields, but not with the usual table load (since the fields are calculated). It needs to get them via the entity, which presumably needs to be done via a handler.

I'm not sure how to make a handler pull something off of an entity. Calling uc_order_product_load() in the render() function seems pretty wasteful, especially considering that the table lookup has already been done and that all three fields will need that stuff.

Anyone know the right way to do this? I can't get views_handler_field_entity to work.

I'm getting closer.

The key seems to be to use entity_views_handler_field_numeric.

This works fine on normal rows. It seems to be using the getter callback to fill in the values.

It does not work on aggregated rows. What am I missing? Do I need to somehow use the entity_views_plugin_row_entity_view?

First, the grouping is all wrong. It should be grouping just on a couple of fields in a related node. It does that correctly before I add the total_cost (Views) field. After I add it, though, lots of additional rows appear, the numbers are wrong, and this shows up in the query:

GROUP BY <valid fields removed>, order_product_id

What's going on here?

StatusFileSize
new4.69 KB
PASSED: [[SimpleTest]]: [MySQL] 2,798 pass(es).
[ View ]

Here's a patch that adds total_price and total_cost properties/fields to the entity and view. It doesn't aggregate.

Anyone know how to get this to work?

Status:Active» Needs review

This patch isn't the final answer, but it's worth a look to see if this is the right approach.

From what I have read so far I think aggregation works only at the SQL level, so you can't use the result of a field handler, entity property, field, or anything similar, as it's calculated too late. In the above code I think the Views field is working at the entity level, and it doesn't know any better than to try and group by the entity itself (hence order_product_id), but obviously this isn't what is actually intended.

I found some sample field handler code at #1219356-8: Best ways to compute and aggregate views fields which we might be able to use to calculate sell_price * qty and cost * qty in SQL, this might then work with aggregation, grouping, etc.

StatusFileSize
new2.67 KB
PASSED: [[SimpleTest]]: [MySQL] 2,796 pass(es).
[ View ]

This looks promising.

Correct. I confirmed that Views aggregation indeed DOES NOT work with computed fields. It is entirely handled by SQL.

I ended up doing pretty much what you did, on a different path. I used Views Raw SQL and the patch at #1880612: Views aggregation fails. With the patch, aggregation DOES work on the resulting expression.

It would be much better to make it work out of the box with Ubercart, so I'll look at your patch.

The computed properties might still be useful for Rules, etc. Should they be left in?

StatusFileSize
new7.35 KB
PASSED: [[SimpleTest]]: [MySQL] 2,798 pass(es).
[ View ]

Extending #13, I added sort and filter handlers, and also a total_weight field (you know someone will eventually ask for it).

Everything seems to work exactly as it should.

Although tempted, I did not add sorting or filtering on the weight field, because it would give erroneous results if someone used multiple weight units. Some complex SQL could do the unit conversion, but that's an issue for another day.

This is a big step toward replacing the built-in reports with Views.

This needs a few more comments. If this approach looks good, let me know, and I'll make another patch with finished documentation.

This looks good to me. I agree with adding the total weight field, and thanks for adding the sort and filter handlers. I am not even sure any more comments are needed, but if you have some more docs to add please do.

For weight conversion, I think perhaps we could handle it in SQL with a lookup table that contains all the conversion constants and JOINs and WHEREs to select the conversion for every row, but that's for another issue.

StatusFileSize
new7.51 KB
PASSED: [[SimpleTest]]: [MySQL] 2,798 pass(es).
[ View ]

Ok, here it is with the updated comments.

StatusFileSize
new7.51 KB
PASSED: [[SimpleTest]]: [MySQL] 2,796 pass(es).
[ View ]

This makes the new field names more consistent.

Are the names of all the handlers OK? The handler for total_weight is uc_order_handler_field_weight_total because it extends uc_product_handler_field_weight, but then we have total_weight and weight_total. If we change it to uc_product_handler_field_total_weight, we should probably change uc_order_handler_field_money_total to uc_order_handler_field_total_money.

StatusFileSize
new7.51 KB
PASSED: [[SimpleTest]]: [MySQL] 2,804 pass(es).
[ View ]

Oops, wrong patch.

Status:Needs review» Fixed

The handler names are fine by me. The class names are only used internally, or in the case where a developer wants to extend them, so as long as they are reasonably consistent it doesn't matter too much.

Committed to 7.x-3.x, as always thanks for your help with this.

You're welcome, as always.

Is it worth adding the fields as computed properties of the entity? That would make them available to Rules, etc., without needing a programmed Rules condition. However, I don't personally have a need for this outside of Views.

Ordered Product entities should be fieldable. There are some edge use cases that would work best with Computed Fields, and that module only works on fieldable entities. I'll make a separate issue for that.

Status:Fixed» Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.