Contrib.social

View with aggregation and exposed filter not working

Open on Drupal.org →
Created on 15 May 2020, about 4 years ago
Updated 24 May 2023, about 1 year ago

In our site we have several content types with a status field that can be "Open", "Closed", or "Cancelled". For each content type, we have a view showing active nodes (status="Open") and "Archived" nodes (status<>"Open"). All Archived views also have an exposed filter on status allowing the user to select either "Closed" or "Cancelled".

With a bit of tweaking, those views work on almost all of our content types. However, there's one view that is using aggregation because it is also allowing an exposed filter on a field from a "child" content type (one-to-many relationship). In order to keep the view from showing duplicates (using the "Distinct" query setting didn't work in this case), we had to turn on aggregation.

These screenshots show the non-exposed and exposed filters set the way they are in other similar views:

The problem with this configuration is the status column is blank. It does seem to be getting the correct results though:

  • Unfiltered: 6989 rows
  • "Closed": 6850 rows
  • "Cancelled": 139 rows

Not sure why status is blank - here's the resulting query, in case that helps:

SELECT node__field_invoice_id.field_invoice_id_value AS node__field_invoice_id_field_invoice_id_value, node__field_date.field_date_value AS node__field_date_field_date_value, node__field_status.field_status_value AS node__field_status_field_status_value, node__field_customer.field_customer_target_id AS node__field_customer_field_customer_target_id, node__field_invoice_total.field_invoice_total_value AS node__field_invoice_total_field_invoice_total_value, MIN(node_field_data.nid) AS nid, MIN(node_field_data_node__field_customer.nid) AS node_field_data_node__field_customer_nid, MIN(field_invoice_node_field_data.nid) AS field_invoice_node_field_data_nid
FROM
{node_field_data} node_field_data
LEFT JOIN {node__field_customer} node__field_customer ON node_field_data.nid = node__field_customer.entity_id AND node__field_customer.deleted = '0' AND (node__field_customer.langcode = node_field_data.langcode OR node__field_customer.bundle = 'sales_contract')
LEFT JOIN {node_field_data} node_field_data_node__field_customer ON node__field_customer.field_customer_target_id = node_field_data_node__field_customer.nid
LEFT JOIN {node__field_invoice} node__field_invoice ON node_field_data.nid = node__field_invoice.field_invoice_target_id AND node__field_invoice.deleted = '0'
LEFT JOIN {node_field_data} field_invoice_node_field_data ON node__field_invoice.entity_id = field_invoice_node_field_data.nid
LEFT JOIN {node__field_status} node__field_status ON node_field_data.nid = node__field_status.entity_id AND node__field_status.field_status_value = 'Open'
LEFT JOIN {node__field_invoice_id} node__field_invoice_id ON node_field_data.nid = node__field_invoice_id.entity_id AND node__field_invoice_id.deleted = '0'
LEFT JOIN {node__field_date} node__field_date ON node_field_data.nid = node__field_date.entity_id AND node__field_date.deleted = '0' AND (node__field_date.langcode = node_field_data.langcode OR node__field_date.bundle = 'sales_contract')
LEFT JOIN {node__field_invoice_total} node__field_invoice_total ON node_field_data.nid = node__field_invoice_total.entity_id AND node__field_invoice_total.deleted = '0'
WHERE ((node__field_status.field_status_value IS NULL)) AND (node_field_data.type IN ('invoice'))
GROUP BY node__field_invoice_id_field_invoice_id_value, node__field_date_field_date_value, node__field_status_field_status_value, node__field_customer_field_customer_target_id, node__field_invoice_total_field_invoice_total_value
ORDER BY node__field_invoice_id_field_invoice_id_value DESC
LIMIT 25 OFFSET 0

...

If we change the non-exposed filter to this:

...and leave the exposed one the same, we get values in the status column. The unfiltered view works, as does the one filtered on "Closed". However, filtering on "Cancelled" returns no data.

This is the unfiltered query:

SELECT node__field_invoice_id.field_invoice_id_value AS node__field_invoice_id_field_invoice_id_value, node__field_date.field_date_value AS node__field_date_field_date_value, node__field_status.field_status_value AS node__field_status_field_status_value, node__field_customer.field_customer_target_id AS node__field_customer_field_customer_target_id, node__field_invoice_total.field_invoice_total_value AS node__field_invoice_total_field_invoice_total_value, MIN(node_field_data.nid) AS nid, MIN(node_field_data_node__field_customer.nid) AS node_field_data_node__field_customer_nid, MIN(field_invoice_node_field_data.nid) AS field_invoice_node_field_data_nid
FROM
{node_field_data} node_field_data
LEFT JOIN {node__field_customer} node__field_customer ON node_field_data.nid = node__field_customer.entity_id AND node__field_customer.deleted = '0' AND (node__field_customer.langcode = node_field_data.langcode OR node__field_customer.bundle = 'sales_contract')
LEFT JOIN {node_field_data} node_field_data_node__field_customer ON node__field_customer.field_customer_target_id = node_field_data_node__field_customer.nid
LEFT JOIN {node__field_invoice} node__field_invoice ON node_field_data.nid = node__field_invoice.field_invoice_target_id AND node__field_invoice.deleted = '0'
LEFT JOIN {node_field_data} field_invoice_node_field_data ON node__field_invoice.entity_id = field_invoice_node_field_data.nid
INNER JOIN {node__field_status} node__field_status ON node_field_data.nid = node__field_status.entity_id AND node__field_status.deleted = '0' AND (node__field_status.langcode = node_field_data.langcode OR node__field_status.bundle = 'shipment')
LEFT JOIN {node__field_invoice_id} node__field_invoice_id ON node_field_data.nid = node__field_invoice_id.entity_id AND node__field_invoice_id.deleted = '0'
LEFT JOIN {node__field_date} node__field_date ON node_field_data.nid = node__field_date.entity_id AND node__field_date.deleted = '0' AND (node__field_date.langcode = node_field_data.langcode OR node__field_date.bundle = 'sales_contract')
LEFT JOIN {node__field_invoice_total} node__field_invoice_total ON node_field_data.nid = node__field_invoice_total.entity_id AND node__field_invoice_total.deleted = '0'
WHERE ((node__field_status.field_status_value IN('Closed', 'Cancelled'))) AND (node_field_data.type IN ('invoice'))
GROUP BY node__field_invoice_id_field_invoice_id_value, node__field_date_field_date_value, node__field_status_field_status_value, node__field_customer_field_customer_target_id, node__field_invoice_total_field_invoice_total_value
ORDER BY node__field_invoice_id_field_invoice_id_value DESC
LIMIT 25 OFFSET 0

This is the query filtered on "Closed":

SELECT node__field_invoice_id.field_invoice_id_value AS node__field_invoice_id_field_invoice_id_value, node__field_date.field_date_value AS node__field_date_field_date_value, node__field_status.field_status_value AS node__field_status_field_status_value, node__field_customer.field_customer_target_id AS node__field_customer_field_customer_target_id, node__field_invoice_total.field_invoice_total_value AS node__field_invoice_total_field_invoice_total_value, MIN(node_field_data.nid) AS nid, MIN(node_field_data_node__field_customer.nid) AS node_field_data_node__field_customer_nid, MIN(field_invoice_node_field_data.nid) AS field_invoice_node_field_data_nid
FROM
{node_field_data} node_field_data
LEFT JOIN {node__field_customer} node__field_customer ON node_field_data.nid = node__field_customer.entity_id AND node__field_customer.deleted = '0' AND (node__field_customer.langcode = node_field_data.langcode OR node__field_customer.bundle = 'sales_contract')
LEFT JOIN {node_field_data} node_field_data_node__field_customer ON node__field_customer.field_customer_target_id = node_field_data_node__field_customer.nid
LEFT JOIN {node__field_invoice} node__field_invoice ON node_field_data.nid = node__field_invoice.field_invoice_target_id AND node__field_invoice.deleted = '0'
LEFT JOIN {node_field_data} field_invoice_node_field_data ON node__field_invoice.entity_id = field_invoice_node_field_data.nid
INNER JOIN {node__field_status} node__field_status ON node_field_data.nid = node__field_status.entity_id AND node__field_status.deleted = '0' AND (node__field_status.langcode = node_field_data.langcode OR node__field_status.bundle = 'shipment')
LEFT JOIN {node__field_status} node__field_status2 ON node_field_data.nid = node__field_status2.entity_id AND node__field_status2.field_status_value != 'Cancelled'
LEFT JOIN {node__field_invoice_id} node__field_invoice_id ON node_field_data.nid = node__field_invoice_id.entity_id AND node__field_invoice_id.deleted = '0'
LEFT JOIN {node__field_date} node__field_date ON node_field_data.nid = node__field_date.entity_id AND node__field_date.deleted = '0' AND (node__field_date.langcode = node_field_data.langcode OR node__field_date.bundle = 'sales_contract')
LEFT JOIN {node__field_invoice_total} node__field_invoice_total ON node_field_data.nid = node__field_invoice_total.entity_id AND node__field_invoice_total.deleted = '0'
WHERE (((node__field_status.field_status_value IN('Closed', 'Cancelled'))) AND ((node__field_status2.field_status_value = 'Closed'))) AND (node_field_data.type IN ('invoice'))
GROUP BY node__field_invoice_id_field_invoice_id_value, node__field_date_field_date_value, node__field_status_field_status_value, node__field_customer_field_customer_target_id, node__field_invoice_total_field_invoice_total_value
ORDER BY node__field_invoice_id_field_invoice_id_value DESC
LIMIT 25 OFFSET 0

This is the query filtered on "Cancelled":

SELECT node__field_invoice_id.field_invoice_id_value AS node__field_invoice_id_field_invoice_id_value, node__field_date.field_date_value AS node__field_date_field_date_value, node__field_status.field_status_value AS node__field_status_field_status_value, node__field_customer.field_customer_target_id AS node__field_customer_field_customer_target_id, node__field_invoice_total.field_invoice_total_value AS node__field_invoice_total_field_invoice_total_value, MIN(node_field_data.nid) AS nid, MIN(node_field_data_node__field_customer.nid) AS node_field_data_node__field_customer_nid, MIN(field_invoice_node_field_data.nid) AS field_invoice_node_field_data_nid
FROM
{node_field_data} node_field_data
LEFT JOIN {node__field_customer} node__field_customer ON node_field_data.nid = node__field_customer.entity_id AND node__field_customer.deleted = '0' AND (node__field_customer.langcode = node_field_data.langcode OR node__field_customer.bundle = 'sales_contract')
LEFT JOIN {node_field_data} node_field_data_node__field_customer ON node__field_customer.field_customer_target_id = node_field_data_node__field_customer.nid
LEFT JOIN {node__field_invoice} node__field_invoice ON node_field_data.nid = node__field_invoice.field_invoice_target_id AND node__field_invoice.deleted = '0'
LEFT JOIN {node_field_data} field_invoice_node_field_data ON node__field_invoice.entity_id = field_invoice_node_field_data.nid
INNER JOIN {node__field_status} node__field_status ON node_field_data.nid = node__field_status.entity_id AND node__field_status.deleted = '0' AND (node__field_status.langcode = node_field_data.langcode OR node__field_status.bundle = 'shipment')
LEFT JOIN {node__field_status} node__field_status2 ON node_field_data.nid = node__field_status2.entity_id AND node__field_status2.field_status_value != 'Cancelled'
LEFT JOIN {node__field_invoice_id} node__field_invoice_id ON node_field_data.nid = node__field_invoice_id.entity_id AND node__field_invoice_id.deleted = '0'
LEFT JOIN {node__field_date} node__field_date ON node_field_data.nid = node__field_date.entity_id AND node__field_date.deleted = '0' AND (node__field_date.langcode = node_field_data.langcode OR node__field_date.bundle = 'sales_contract')
LEFT JOIN {node__field_invoice_total} node__field_invoice_total ON node_field_data.nid = node__field_invoice_total.entity_id AND node__field_invoice_total.deleted = '0'
WHERE (((node__field_status.field_status_value IN('Closed', 'Cancelled'))) AND ((node__field_status2.field_status_value = 'Cancelled'))) AND (node_field_data.type IN ('invoice'))
GROUP BY node__field_invoice_id_field_invoice_id_value, node__field_date_field_date_value, node__field_status_field_status_value, node__field_customer_field_customer_target_id, node__field_invoice_total_field_invoice_total_value
ORDER BY node__field_invoice_id_field_invoice_id_value DESC
LIMIT 25 OFFSET 0

The issue seems to be this part from each of the two filtered queries...

From the one filtered on "Closed":

LEFT JOIN {node__field_status} node__field_status2 ON node_field_data.nid = node__field_status2.entity_id AND node__field_status2.field_status_value != 'Cancelled'

From the one filtered on "Cancelled":

LEFT JOIN {node__field_status} node__field_status2 ON node_field_data.nid = node__field_status2.entity_id AND node__field_status2.field_status_value != 'Cancelled'

Notice they both say "!= 'Cancelled'". Clearly, the second one should be "!= 'Closed'". The WHERE clauses in both cases seem to be correct.

🐛 Bug report
Status

Needs work

Version

9.4

Component
Views  →

Last updated about 4 hours ago

Created by

🇺🇸United States ExTexan

Live updates comments and jobs are added and updated live.
  • Needs issue summary update

    Issue summaries save everyone time if they are kept up-to-date. See Update issue summary task instructions.

Sign in to follow issues

Comments & Activities

Not all content is available!

It's likely this issue predates Contrib.social: some issue and comment data are missing.

  • Status changed to Needs work over 1 year ago
  • Status changed to Closed: outdated over 1 year ago
  • Status changed to Needs work over 1 year ago
  • Comment about 1 year ago →
    🇺🇸United States ExTexan

    I'm uploading screenshots that provide a better visualization of one of the two issues stated in the OP. The view is currently configured the 2nd way - which appears below the line "If we change the non-exposed filter to this:" in the OP.

    In the view, there are two filters on the "status" field, one that's not exposed that restricts the list to statuses "Closed" and "Cancelled" (excludes "Open"). The other filter is exposed and allows the user to filter on just "Closed", or just "Cancelled" or "Any".

    Here's the non-exposed filter:

    Here's the exposed filter:

    The two screenshots below highlight the differences between an unfiltered query diffed to one filtered on "Closed", and the same unfiltered query diffed to the one filtered on "Cancelled". And, again, with this configuration, the unfiltered query works correctly, as does the one filtered on "Closed", while the one filtered on "Cancelled" shows no results. And the reason why is obvious when you look at the screenshots.

    Here's the one filtered on "Closed":

    Here's the one filtered on "Cancelled". Note the red box around the comment indicating the bug.

    In previous posts, I've been asked to provide a simplified setup that reproduces the error. I can't see any way to do that. The system this view is for is very complex, with many tables and fields. But as these latest screenshots illustrate, the bug is in the code that adds the additional LEFT JOIN for the status field/table (called "status2" in the queries). Its "ON" clause seems to be getting the first of the two possible values of status in both cases, when it should be getting the opposite one from what's included in the "WHERE" clause. Hopefully that narrows the search a bit.

    To restate the bug, in the query that doesn't work, it's trying to select when status equals "Cancelled" AND when status doesn't equal "Cancelled", which just isn't possible.

contrib.social Blog FAQ Discussions
Production build 0.69.0 2024