12

EntityFieldQuery is not intended to be used to run aggregate functions (SUM, AVG, etc.) on field data because it is SQL-agnostic. That said, such operations legitimately need to be run from time to time. According to SQL function with EntityFieldQuery and especially EntityFieldQuery and how to use aggregate functions SUM, ARG and MAX, SQL queries need to be used, and that approach fits my use case the best. I was speaking with @chx yesterday and he recommended using a couple of internal functions to find the field name and column name. I'm just wondering if this is sustainable and if it's appropriate to do in a codebase that I might release to otehrs.

If it's the best way, then it's the best way. I just don't want to do it before I'm absolutely sure because it feels messy.

  • For now, I've just used the internal functions (_field_sql_storage_tablename($field) and _field_sql_storage_columnname($field_name, $column), which suits my current needs, but it isn't sustainable, so still interested in an answer to this question should one come. – wizonesolutions Oct 29 '11 at 22:42
2

The View field module allows you to expose field tables as base tables to views. This differs from Views default behavior in that the base table is the field table and not an entity to which the field data is then loaded from. For example when selecting node as a base table you can then add fields, but the primary (base table) in the query is still node which depending on your data can screw up the aggregate functions (aka a many-to-one relationship of some fields to the node).

Views field allows you direct access to the field tables which means aggregate functions work properly. Additionally if you need to do "interesting" joins to other field tables you can control them completely using the following.

/**
 * Implements hook_views_data_alter().
 */
function mymodule_views_data_alter(&$data) {
  views_field_add_multi_join($data, /* see docs */);
}

Which is fairly simple to use and allows you to then perform aggregate functions across multiple field tables at once. You can then manually invoke a view $view->execute() and pull the results off the view. There are examples of this in the views documentation.

The benefits of this approach over EntityFieldQuery is that you can manage the process in Views (which almost everyone will already be using) and allow it to perform the physical query building in less direct way which helps elevate possible breakage down the road. In addition many times you will want to display such aggregate data on an admin screen which you can then use the view to provide both a display and access the results in code for additional purposes.

|improve this answer|||||
  • I'll have to check this out. I could swap that code into my computed fields instead of doing the query manually. I'll probably be working on the site where I need this soon and will report back at some point. If it works, I'll accept. – wizonesolutions Feb 29 '12 at 3:20
0

Could this be done in an add-on module to views? It handles queries very well and some results are published with aggregate functions such as in actions when contextual filters fail where it can default to all values and give counts on the available results in each. The functions for the queries should be there but a UI form would be needed for it. Let me know if this makes sense. I am just beginning to dissect views and am speculating.

|improve this answer|||||
  • COUNT queries can be run on entities, so that's solved. I'm more interested in SUM myself. views_calc may provide insight, but I want to use the value in a Computed Field...which I could well do with views_get_view_result (or similar). However, getting Views to generate that query in the first place is the trick. It seems like the hackish way might be the best. I guess I will start with that because if I don't release it, I'll be golden. If I do, someone will hopefully submit a patch. – wizonesolutions Oct 23 '11 at 6:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.