This seems simple enough, but nothing I've tried with Global Math and Aggregation works.

I have a table of records. Each record has a sell price value and a quantity value. I want to multiply the sell price by the quantity, and sum the results to get total sales.

How is this done? All my experiments with grouping, summing, and math give the wrong answer.

Note: I'm grouping on some text fields, and each group should have a separate total sales number.

Comments

merlinofchaos’s picture

merlinofchaos commented
Status: Active » Fixed

In order to do this, you need to have a field that is a formula, which means you'd need a custom field to do this; this operation is not supported via the UI.

As an alternative, you can add a computed field which is computed as price * quantity and stored in the database. Then you can just sum that field. That is the simplest (and best performing) way to accomplish this task.

DanZ’s picture

DanZ commented

Thanks for the response.

By "custom field", do you mean that I'd need to add a custom field handler? Is there any documentation on how to do this for Views 3? I could only find docs for Views 2.

By "computed field", do you mean http://drupal.org/project/computed_field? This is for Ubercart and the transaction records are not nodes. Would it still work?

merlinofchaos’s picture

merlinofchaos commented

It'll work if the transaction records are entities, which they may not be.

The documentation for Views 2 and custom fields should be the same for Views 3 -- the only thing that's different is handling of aggregation. If you inherit your field from the formula field, it should be a relatively small piece of code to just write the formula to multiply the two fields together.

DanZ’s picture

DanZ commented

I appreciate the tips. I'm still banging my head against this one.

The transaction records are, in fact, entities, although not fieldable.

I modified the entity to add a computed total_price property (and total_cost, and total_weight). Calling entity_load() fills in the value. This works fine.

What's the right way to use that entity in a view, then?

Here's what I've tried so far:

I wrote a custom field handler for the field. Its pre-render does an entity_load() if the data isn't there. Its query() adds the ID field for the entity (so entity_load() can work), and also sets $this->field_alias to $this->real_field.

I updated the hook_views_data() implementation to add the field.

It all works great on individual records. If I try to aggregate, though, it does a sum on the ID field and tries to entity_load on that. Not good. I'm thinking that this is not the right approach.

Since the module implements hook_views_data(), I can't use EntityDefaultViewsController, right?

I'm thinking that there must be some clean, simple way for Views to deal with entities with non-DB fields, but I'm just missing it.

DanZ’s picture

DanZ commented
Status: Fixed » Active
DanZ’s picture

DanZ commented

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 views. What am I missing? Do I need to somehow use the entity_views_plugin_row_entity_view? Something else?

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?

DanZ’s picture

DanZ commented
Status: Active » Fixed

The answer is: It can't do that.

Views does all of its standard aggregation via SQL. So, for normal Views aggregation to work, the value has to be in the database or calculated with an SQL formula. This does not work with entity properties with computed values that don't touch the database.

As suggested in #1, attaching a computed field to an entity can put the value in the database, and does work with aggregation. It does require more DB space, an additional JOIN, a fieldable entity, and an additional module, though.

It is probably possible to use some special handler to compute the aggregated value with PHP and put that value into the Views field, but I don't know how to do that. I believe that this is being done for entities in Drupal 8.

Status: Fixed » Closed (fixed)

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