Problem 1: The Views/global filter Filter Fields comparison doesn't use the aggregation settings for the selected fields. The fields selection UI pretends it does:

screen-views-compare-fields-filter

but the resulting query doesn't use COUNT, because the handler's query() doesn't consult aggregation settings (like views_plugin_query_default::compile_fields() does).

Solution: use the same logic to apply aggregation functions to selected fields.

Problem 2: you can't use COUNT in WHERE if the WHERE would influence the COUNT result.
Solution: the condition has to move to a HAVING, which is no problem.

Problem 3: If the condition is grouped (in an OR for instance) with another field, that is NOT in the HAVING, the OR would stretch across WHERE and HAVING, which is impossible.
Solution: Move the entire group to HAVING if any of its conditions requires HAVING.

Yes, problems. I have a patch that fixes problems 1 and 2, but not 3, because that's a very tiny potential problem. It totally messes up the query though. Not fatal, but it won't do what you want.

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

rudiedirkx’s picture

Status: Active » Needs review
FileSize
2.21 KB

Patch to fix problems 1 and 2.

I might look into problem 3 later, but probably not worth it.

Darren Oh’s picture

Title: Filter _Fields comparison_ doesn't use aggregation settings » Fix fields comparison filter ignoring aggregation
Status: Needs review » Reviewed & tested by the community
FileSize
2.62 KB

First patch no longer applies. New patch attached. By the way, this is a good fix.

DamienMcKenna’s picture

Status: Reviewed & tested by the community » Needs work
Issue tags: +Needs tests

Thanks for the work on this.

I think we should add test coverage for this to make sure the bug is fully fixed and that it doesn't come back again.