You are viewing a plain text version of this content. The canonical link for it is here.

Posted to notifications@superset.apache.org by "aamir4813 (via GitHub)" <gi...@apache.org> on 2024/05/17 12:17:39 UTC

[I] "Drill to Details by" Feature Ignores Applied Filters in Superset 4.0.0 [superset]

aamir4813 opened a new issue, #28562:
URL: https://github.com/apache/superset/issues/28562

### Bug description

In Superset, the "Drill to details by" feature does not apply filters correctly when generating the SQL query. If used without any filters, all raw data is displayed correctly. However, when filters are applied and the feature is used, the generated SQL query does not include the filters, resulting in the display of raw records instead of the filtered records in the drill down.

### How to reproduce the bug

1. **Create a Dataset:**
```sql
SalesDataset:
SELECT
region,
customer_name,
sales_usd
FROM public.sales
WHERE 1 = 1
{% if filter_values('company')|length %}
AND company IN {{ filter_values('company')|where_in }}
{% endif %}
```

2. **Create a Dataset for Filter:**
```sql
CompanyFilterDataSet:
SELECT DISTINCT company AS company
FROM public.sales
```

3. **Create a Pivot Table Chart:**
- **DataSet:** SalesDataset
- **Rows:** region
- **Column:** customer_name
- **Metric:** count(sales_usd)

4. **Create a Dashboard Filter:**
- **Source: CompanyFilterDataSet
- **Column: company

5. **Test without Applying Filter:
- **Use the "Drill to details by all" feature.
- **The drill down shows the correct number of records.
6. **Apply Filter and Test Again:**
- **Apply a filter using the CompanyFilterDataSet.
- **Notice that the values in the chart change according to the filter.
- **Use the "Drill to details by all" feature.
- **The drill down shows the original raw records (without the filter) instead of the filtered records.

# Expected Behavior
The generated SQL query for the drill down should include the applied filters, and the drill down should display the filtered records.

### Screenshots/recordings

![ScreenRecording2024-05-17at5 36 03PM-ezgif com-video-to-gif-converter](https://github.com/apache/superset/assets/25955159/b667d922-1c46-457b-b243-ecde034fba73)

### Superset version

4.0.1

### Python version

3.11

### Node version

18 or greater

### Browser

Chrome

### Additional context

## When creating a dashboard, I performed the following steps:

- Created a dataset `SalesDataset` with a filter condition for company.
- Created another dataset CompanyFilterDataSet for filtering companies.
- Built a pivot table chart using SalesDataset, with `region as rows, customer_name as columns, and count(sales_usd)` as the metric.
- Added a dashboard filter using `CompanyFilterDataSet` on the `company` column.
- Initially, without applying any filters, the drill down feature shows the correct number of records. However, when a filter is applied, although the chart updates correctly, the drill down shows the original unfiltered records.

### Checklist

- [X] I have searched Superset docs and Slack and didn't find a solution to my problem.
- [X] I have searched the GitHub issue tracker and didn't find a similar bug report.
- [X] I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.

--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org

Re: [I] "Drill to Details by" Feature Ignores Applied Filters in Superset 4.0.1 [superset]

Posted by "aamir4813 (via GitHub)" <gi...@apache.org>.

aamir4813 commented on issue #28562:
URL: https://github.com/apache/superset/issues/28562#issuecomment-2121980859

   Hi ,
   This is not related to the #27941 (i've checked the problem statement of this fix)


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org

Re: [I] "Drill to Details by" Feature Ignores Applied Filters in Superset 4.0.1 [superset]

Posted by "dosubot[bot] (via GitHub)" <gi...@apache.org>.

dosubot[bot] commented on issue #28562:
URL: https://github.com/apache/superset/issues/28562#issuecomment-2122001584

   <!-- Answer -->
   To resolve the issue where the "Drill to details by" feature in Apache Superset version 4.0.1 ignores applied filters, ensure that filters are correctly passed and applied both in the frontend and backend.
   
   ### Frontend Changes
   
   1. **Pass Filters to `DrillDetailMenuItems` Component:**
      Ensure the `filters` object contains the correct filter values and is passed to the `DrillDetailMenuItems` component.
   
      ```tsx
      if (showDrillToDetail) {
          menuItems.push(
            <DrillDetailMenuItems
              chartId={id}
              formData={formData}
              filters={filters?.drillToDetail}
              isContextMenu
              contextMenuY={clientY}
              onSelection={onSelection}
              submenuIndex={showCrossFilters ? 2 : 1}
              showModal={drillModalIsOpen}
              setShowModal={setDrillModalIsOpen}
              {...(additionalConfig?.drillToDetail || {})}
            />,
          );
        }
      ```
   
   2. **Verify `handleContextMenu` Function:**
      Ensure the `handleContextMenu` function constructs the `drillToDetailFilters` based on the selected filters.
   
      ```tsx
      const handleContextMenu = useCallback(
          (
            e: MouseEvent,
            colKey: (string | number | boolean)[] | undefined,
            rowKey: (string | number | boolean)[] | undefined,
            dataPoint: { [key: string]: string },
          ) => {
            if (onContextMenu) {
              e.preventDefault();
              e.stopPropagation();
              const drillToDetailFilters: BinaryQueryObjectFilterClause[] = [];
              if (colKey && colKey.length > 1) {
                colKey.forEach((val, i) => {
                  const col = cols[i];
                  const formatter = dateFormatters[col];
                  const formattedVal = formatter?.(val as number) || String(val);
                  if (i > 0) {
                    drillToDetailFilters.push({
                      col,
                      op: '==',
                      val,
                      formattedVal,
                      grain: formatter ? timeGrainSqla : undefined,
                    });
                  }
                });
              }
              if (rowKey) {
                rowKey.forEach((val, i) => {
                  const col = rows[i];
                  const formatter = dateFormatters[col];
                  const formattedVal = formatter?.(val as number) || String(val);
                  drillToDetailFilters.push({
                    col,
                    op: '==',
                    val,
                    formattedVal,
                    grain: formatter ? timeGrainSqla : undefined,
                  });
                });
              }
              onContextMenu(e.clientX, e.clientY, {
                drillToDetail: drillToDetailFilters,
                crossFilter: getCrossFilterDataMask(dataPoint),
                drillBy: dataPoint && {
                  filters: [
                    {
                      col: Object.keys(dataPoint)[0],
                      op: '==',
                      val: Object.values(dataPoint)[0],
                    },
                  ],
                  groupbyFieldName: rowKey ? 'groupbyRows' : 'groupbyColumns',
                },
              });
            }
          },
          [
            cols,
            dateFormatters,
            getCrossFilterDataMask,
            onContextMenu,
            rows,
            timeGrainSqla,
          ],
        );
      ```
   
   ### Backend Changes
   
   1. **Apply Filters in `_get_drill_detail` Function:**
      Ensure the `_get_drill_detail` function in `superset/common/query_actions.py` correctly applies the filters to the query object.
   
      ```py
      def _get_drill_detail(
          query_context: QueryContext, query_obj: QueryObject, force_cached: bool = False
      ) -> dict[str, Any]:
          datasource = _get_datasource(query_context, query_obj)
          query_obj = copy.copy(query_obj)
          query_obj.is_timeseries = False
          query_obj.orderby = []
          query_obj.metrics = None
          query_obj.post_processing = []
          qry_obj_cols = []
          for o in datasource.columns:
              if isinstance(o, dict):
                  qry_obj_cols.append(o.get("column_name"))
              else:
                  qry_obj_cols.append(o.column_name)
          query_obj.columns = qry_obj_cols
          return _get_full(query_context, query_obj, force_cached)
      ```
   
   2. **Check `QueryContext` Object:**
      Ensure the `QueryContext` object handles filters correctly when generating SQL queries for drill-down details.
   
      ```python
      def _apply_filters(self, query_object: QueryObject) -> None:
          if query_object.time_range:
              for filter_object in query_object.filter:
                  if filter_object["op"] == "TEMPORAL_RANGE":
                      filter_object["val"] = query_object.time_range
      ```
   
   3. **Verify `buildQuery` Function:**
      Ensure the `buildQuery` function in `superset-frontend/src/filters/components/Select/buildQuery.ts` correctly adds extra filters based on user input.
   
      ```ts
      const buildQuery: BuildQuery<PluginFilterSelectQueryFormData> = (
        formData: PluginFilterSelectQueryFormData,
        options,
      ) => {
        const { search, coltypeMap } = options?.ownState || {};
        const { sortAscending, sortMetric } = { ...DEFAULT_FORM_DATA, ...formData };
        return buildQueryContext(formData, baseQueryObject => {
          const { columns = [], filters = [] } = baseQueryObject;
          const extraFilters: QueryObjectFilterClause[] = [];
          if (search) {
            columns.filter(isPhysicalColumn).forEach(column => {
              const label = getColumnLabel(column);
              if (
                coltypeMap[label] === GenericDataType.String ||
                (coltypeMap[label] === GenericDataType.Numeric &&
                  !Number.isNaN(Number(search)))
              ) {
                extraFilters.push({
                  col: column,
                  op: 'ILIKE',
                  val: `%${search}%`,
                });
              }
            });
          }
          const sortColumns = sortMetric ? [sortMetric] : columns;
          const query: QueryObject[] = [
            {
              ...baseQueryObject,
              columns,
              metrics: sortMetric ? [sortMetric] : [],
              filters: filters.concat(extraFilters),
              orderby:
                sortMetric || sortAscending !== undefined
                  ? sortColumns.map(column => [column, !!sortAscending])
                  : [],
            },
          ];
          return query;
        });
      };
      ```
   
   By ensuring that filters are correctly passed and applied at each step, you should be able to resolve the issue where the "Drill to details by" feature ignores the applied filters.
   
   
   <!-- Dosu Comment Footer -->
   <details>
   <summary>
   
   #### Details
   </summary>
   
   **Sources**
   - [Drill by function error with caching enabled](https://github.com/apache/superset/issues/26471)
   - [superset-frontend/src/components/Chart/ChartContextMenu/ChartContextMenu.tsx](https://github.com/apache/superset/blob/master/superset-frontend/src/components/Chart/ChartContextMenu/ChartContextMenu.tsx)
   - [superset-frontend/plugins/plugin-chart-pivot-table/src/PivotTableChart.tsx](https://github.com/apache/superset/blob/master/superset-frontend/plugins/plugin-chart-pivot-table/src/PivotTableChart.tsx)
   - [superset/common/query_actions.py](https://github.com/apache/superset/blob/master/superset/common/query_actions.py)
   - [superset-frontend/src/components/Chart/DrillDetail/DrillDetailMenuItems.tsx](https://github.com/apache/superset/blob/master/superset-frontend/src/components/Chart/DrillDetail/DrillDetailMenuItems.tsx)
   - [superset-frontend/src/components/Chart/DrillDetail/DrillDetailMenuItems.test.tsx](https://github.com/apache/superset/blob/master/superset-frontend/src/components/Chart/DrillDetail/DrillDetailMenuItems.test.tsx)
   - [superset/common/query_context_factory.py](https://github.com/apache/superset/blob/master/superset/common/query_context_factory.py)
   - [superset/common/query_context.py](https://github.com/apache/superset/blob/master/superset/common/query_context.py)
   - [superset-frontend/src/filters/components/Select/buildQuery.ts](https://github.com/apache/superset/blob/master/superset-frontend/src/filters/components/Select/buildQuery.ts)
   - [tests/integration_tests/query_context_tests.py](https://github.com/apache/superset/blob/master/tests/integration_tests/query_context_tests.py)
   - [superset-frontend/src/filters/components/TimeColumn/buildQuery.ts](https://github.com/apache/superset/blob/master/superset-frontend/src/filters/components/TimeColumn/buildQuery.ts)
   - [superset-frontend/cypress-base/cypress/e2e/dashboard/drilltodetail.test.ts](https://github.com/apache/superset/blob/master/superset-frontend/cypress-base/cypress/e2e/dashboard/drilltodetail.test.ts)
   - [superset-frontend/cypress-base/cypress/e2e/dashboard/drillby.test.ts](https://github.com/apache/superset/blob/master/superset-frontend/cypress-base/cypress/e2e/dashboard/drillby.test.ts)
   - [superset/common/query_context_processor.py](https://github.com/apache/superset/blob/master/superset/common/query_context_processor.py)
   
   <br/>
   
   **About Dosu**
   This response is meant to be useful and save you time. It is **not meant to be a precise solution**, but rather a starting point for your own research.
   
   Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant.
   </details>
   
   *To continue the conversation, mention @dosu.*
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org

Re: [I] "Drill to Details by" Feature Ignores Applied Filters in Superset 4.0.1 [superset]

Posted by "suregoodru (via GitHub)" <gi...@apache.org>.

suregoodru commented on issue #28562:
URL: https://github.com/apache/superset/issues/28562#issuecomment-2145402681

   I have a similar problem. Are there any working solutions?


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org