cube-js / cube-js/cube

Add additional filters to measures which will be passed in where clause in generated SQL

Open
#3,758 5 comments 0 reactions 0 assignees View on GitHub
backend:server help wanted
Dominant language
Rust
Stars
20.8k
Forks
2.1k
Avg merge
1d 2h
Merged PRs (30d)
181

Description

**Is your feature request related to a problem? Please describe.**
For some databases the approach to pass measure.filters inside aggregation function, like `count(CASE ... WHEN ... END)`, might be inefficient, the example of such database is ClickHouse. In ClickHouse case the database scheduler can't optimize such query format and just goes across all table rows that leading to query performance degradation, since the table scans all rows and don't use the underlying table index.
Also, there is a case for PostgresSQL when putting filters into where clause rapidly increases query performance.
For example, we have an order entity in our CRM Postgres database, the order might be in several statuses: CREATED, CANCELED, ASSEMBLY, PAID, ISSUED, RETURNED. And some of our bussiness metrics calculations are based on these statuses, so if we want to calculate measure ```countPaidOrder```, that currently define in Order cube like:
```
type: `count`,
filters: [
{
sql: `${CUBE}.status not in (\'NOT_CREATED\', \'CREATED\', \'RETURNED\', \'CANCELED\')`
}
],
drillMembers: [productId, sellerId, shop_id]
```
Now cube-js generated SQL where filters are wrapped by aggregate function, but if this condition would be passed into where clause query performance will be better.

**Describe the solution you'd like**
We suggest adding an additional measure member that will have the same signature as already exists ```filters``` member but pass all entries into where clause on SQL generation step.
```
type: `count`,
filters: [
{
sql: `some_condition`
}
],
whereFilters: [
{
sql: `${CUBE}.status not in (\'NOT_CREATED\', \'CREATED\', \'RETURNED\', \'CANCELED\')`
}
],
drillMembers: [productId, sellerId, shop_id]
```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.