Is it possible to use the D8 EntityQuery condition and sort functionality to filter/sort based on the outcome of multiple fields (and possible external/3th party value) calculations?

Example with condition:

I want to filter the results based on the average of 5 numeric fields. So i need to add them up and divide them by 5 and if that outcome < 8 i do not want to use the entity.

Example sort with external API value:

I want to sort the results based on a calculation with an external API value. So lets say i have a numeric field in my entity with the value of 10. The external API gives me a value of 30. The calculation would be 30 - 10 = 20.

Now for my second entity the numeric field value equals 5 and the API sais 20. The calculation would be 20-5=15.

Now i want to sort the results from highest to lowest. So in this case the first entity should be first because the outcome of the calculation was 20 and for the second entity it was 15 so it should come second.

  • mmm i fell down this rabbit hole a while ago .. and found easier to get the values then sort them afterwords .. may be the simple option rather than getting in to alot of and or conditions – Taggart Jensen Jun 14 '18 at 0:29
  • @TaggartJensen Thank you for your reply. I know this is a possible solution, however it would take much processing time when there are about 100+ or 1000+ nodes. If there is no easy way to do this i guess i have to implement some sort of cache system and filter/sort on the cached items. – Skywalker Jun 14 '18 at 13:58

Some thoughts...

You should be able to do aggregation in your queries; try looking at QueryAggregateInterface and ConditionAggregateInterface. However this interface does seem to be pretty simple. The Views plugin implements full 'having' and 'group by' functionality, but probably in a way that's difficult to access outside views.

You might investigate using the Symfony Doctrine class or plain PDO to do it the 'old way'.

I am pretty sure you won't be able to calculate based on an external API; the database needs all the values to hand, either in a table or as query-provided data. Some databases do support advanced functionality like REST queries, but any code doing this won't be portable and so won't be available as standard in the Drupal interfaces.

You could possibly do the API lookups in advance (in e.g. php) and then pass them to a temporary table for the DB to check.

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.