5

I have entity type payment and need count all payments (calculate based on field_total), if its hard to undersand, I show it on example:

Payment A - $10 Payment B - $20 Payment C - $30

And I need query that SUM all values. In my example it will be $60.

$query = new EntityFieldQuery();
$entities = $query->entityCondition('entity_type', 'node', '=')
                    ->entityCondition('bundle', 'payment')
                    ->execute();

What is best method to solve this problem? I could select all nids and load it. But this is not very good if I have thousands nodes, or Iam wrong?

Thank you for advice.

6

EntityFieldQuery does not support Aggregations/Expressions.

You have three options, basically:

  • Load all nodes, make the summary in PHP. As you already said, not a good idea if you expected thousands or even just hundreds of payments.

  • Write an SQL query, which should be rather simple since your field is probably only attached to the relevant bundle so you only need to query a single table and get the SUM() of the field value of all values. To be sure, you could also join the node table and add a condition on the node type. Although not nice, doing this is IMHO fine for custom code, where you know that the field will always be in the database. If you want to support different field storage implementations like MongoDB, not so much...

  • Lastly, a bit more work, you could maintain a global summary that you update every time a payment is created/updated by implementing a corresponding node/field hook (like hook_field_insert(), as you can see this is what taxonomy.module does...). This would probably be a good idea if you expected that you need the summary frequently. Also, while it might be tempting to use variable_get()/set to maintain a single value, that's not a good idea as it results in cache clears every time a variable is written.

5

To expand on Berdir's answer, EntityFieldQuery is not an SQL query builder. Sure, core contains an SQL implementation because core happens to ship with a Field SQL storage module. As such, only a very limited set of functionality is provided. In other words, how do you run AVG on the score of your YouTube videos (which could be a field just fine).

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.