Posted by DanZ on January 4, 2013 at 10:46pm
Can Views do a SUM() aggregation on fields that aren't in the database?
I've tried a lot of techniques, but nothing works. Is it even possible?
This is for a computed entity property.
Can Views do a SUM() aggregation on fields that aren't in the database?
I've tried a lot of techniques, but nothing works. Is it even possible?
This is for a computed entity property.
Comments
_
i could swear I just answered this exact question recently, but couldn't find it. No, views aggregation is just Db aggregation (added right to the SQL views generates). You'd either have to use views_php or handle it via a preprocess function or in the template.
_
Don't be a Help Vampire - read and abide the forum guidelines.
If you find my assistance useful, please pay it forward to your fellow drupalers.
You may have answered a
You may have answered a different version of the question, as I've been trying a lot of things. The key point is that Views aggregation is all generated by SQL, and I didn't get that until very recently.
Here are the solutions I've found, none of which is completely satisfactory:
Hack Views Raw SQL and use it to calculate the value. Aggregation will work with the hack (see #1880612: Views aggregation fails. This works for my particular case.
Wait until Drupal 8, which will have proper #1854708: EntityQuery aggregation support.
Use Views PHP. The issue log seems to be fully of nasty bugs. Also, the PHP would have to do the aggregation itself. I don't know how it would get the input to know what to select for. I didn't try.
Make the entity fieldable and use a computed field to contain the values. There are old issues about aggregation not working on fields. It might be fixed by now. I didn't try. If aggregation does work on Fields, this is probably the best general-purpose solution.
Use a preprocess function. I don't know that that is.
Handle it in the template. I don't know what that is.
Have a handler that works like prerender_list, but instead of displaying the list of records, it aggregates them in memory. I don't know what prerender_list is.
--
www.ztwistbooks.com. Math books that are actually fun.
_
I know I've done something similar before with views_php. Can you be more specific about the fields in question and the view you're trying to create? (or point me to the post that has those details).
_
Don't be a Help Vampire - read and abide the forum guidelines.
If you find my assistance useful, please pay it forward to your fellow drupalers.
Thanks for your continued
Thanks for your continued interest.
The idea is that there's a record that has price and quantity fields. I wanted to take the SUM() of price * qty for selected, grouped records. This could calculate, for example, total sales for a particular month, grouped by product.
I tried doing it by setting it up as an entity with a calculated total_price field (using 'getter callback' and/or an attach load). That didn't aggregate because that wasn't in the DB.
I ended up with a solution in #1876496: Calculate additional Ordered Product fields for Views. It finesses the problem by using an SQL formula to calculate the desired value, which does work with SUM() aggregation. It uses custom handlers to insert the formula into the query, and to use the right alias for the sort and filter functionality.
So, the problem is solved for this particular problem because an SQL formula works, but not for the general case.
Doing this with views_php on an arbitrary entity would require somehow getting the entity IDs for all matching records, loading them all, and calculating the SUM() of the appropriate property right? Given that there are some GROUP BY fields in the query, I am not sure how to get that list of IDs. I guess it would require another query with a WHERE clause on the values of earlier rendered values of the View.
--
www.ztwistbooks.com. Math books that are actually fun.
_
That's exactly what I did (server cost * quantity). And I did the math in the view (since I didn't need it on the node) with views_php (just add a PHP field to the view and put the formula in the value textbox).
However, I just tried it with a computed_field for total_cost on the node, and then I COULD use that computed field with views SQL aggregation for SUM in the view(provided the "Store value in the database" option is selected in the computed field settings).
Have you tried that? What's not working?
_
Don't be a Help Vampire - read and abide the forum guidelines.
If you find my assistance useful, please pay it forward to your fellow drupalers.
Yes, storing it in a computed
Yes, storing it in a computed Field in the DB should work with Views aggregation. That was definitely a viable Plan B, and probably the best option for the general case.
The issue there is that this is for Ubercart, and the entity in question is not fieldable (although perhaps it could be made so). Also, we wished to avoid requiring more modules to make this small feature work.
Also, having a whole additional module plus the additional tables and storage (for the Fields) seems like a a lot of resources for integrating one tiny calculation.
--
www.ztwistbooks.com. Math books that are actually fun.
_
I guess it all depends on what you want to do. For me maintaining custom handlers would be far more undesirable than adding computed_field module (which I almost always have installed anyway). But since the entity isn't fieldable (weirdness like that is one of the reasons i've abandoned ubercart in favor of commerce) that point is really moot.
So, if using computed_field or views_php are not desirable, that leaves doing the calc in a custom views template or preprocess function -- but that's for output, so if you're using the calc for other things besides simple display I'm not sure that would be sufficient (though there are a number of levels where you can inject the value so it might work).
Sounds like you've found the only solution that works for your particular use case.
_
Don't be a Help Vampire - read and abide the forum guidelines.
If you find my assistance useful, please pay it forward to your fellow drupalers.
This ended up being a feature
This ended up being a feature that will be added to Ubercart proper, since this will be useful to lots of installs for making sales reports.
Given that the feature is going into the next minor release of Ubercart, making it require an additional module would break thousands of Ubercart installations. Probably not a good idea.
Making that entity fieldable would probably be a good idea, as it would let admins set up their own computed fields. I don't know the full implications of that (if any), though.
--
www.ztwistbooks.com. Math books that are actually fun.