> ## Documentation Index
> Fetch the complete documentation index at: https://cubed3-claude-filter-params-date-injection-pm2qgo.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Context variables

> Context variables like CUBE, FILTER_PARAMS, SQL_UTILS, and COMPILE_CONTEXT are available within cube definitions for dynamic SQL and model generation.

* [`CUBE`](#cube) for [referencing members][ref-syntax-references] of the same cube.
* [`FILTER_PARAMS`](#filter_params) and [`FILTER_GROUP`](#filter_group) for optimizing generated SQL queries.
* [`SQL_UTILS`](#sql_utils) for time zone conversion.
* [`COMPILE_CONTEXT`](#compile_context) for creation of [dynamic data models][ref-dynamic-data-models].

## `CUBE`

You can use the `CUBE` context variable to reference columns or members of
the current cube so you don't have to repeat the its name over and over.

It helps [reference members][ref-syntax-references] while keeping the data
model code DRY and easy to maintain.

<CodeGroup>
  ```yaml title="YAML" theme={"dark"}
  cubes:
    - name: users
      sql_table: users

      joins:
        - name: contacts
          sql: "{CUBE}.contact_id = {contacts.id}"
          relationship: one_to_one

      dimensions:
        - name: id
          sql: "{CUBE}.id"
          type: number
          primary_key: true

        - name: name
          sql: "COALESCE({CUBE.name}, {contacts.name})"
          type: string

    - name: contacts
      sql_table: contacts

      dimensions:
        - name: id
          sql: "{CUBE}.id"
          type: number
          primary_key: true

        - name: name
          sql: "{CUBE}.name"
          type: string









  ```

  ```javascript title="JavaScript" theme={"dark"}
  cube(`users`, {
    sql_table: `users`,

    joins: {
      contacts: {
        sql: `${CUBE}.contact_id = ${contacts.id}`,
        relationship: `one_to_one`
      }
    },

    dimensions: {
      id: {
        sql: `${CUBE}.id`,
        type: `number`,
        primary_key: true
      },

      name: {
        sql: `COALESCE(${CUBE}.name, ${contacts.name})`,
        type: `string`
      }
    }
  })

  cube(`contacts`, {
    sql_table: `contacts`,

    dimensions: {
      id: {
        sql: `${CUBE}.id`,
        type: `number`,
        primary_key: true
      },

      name: {
        sql: `${CUBE}.name`,
        type: `string`
      }
    }
  })
  ```
</CodeGroup>

## `FILTER_PARAMS`

`FILTER_PARAMS` context variable allows you to use [filter][ref-query-filter]
values from the Cube query during SQL generation.

This is useful for hinting your database optimizer to use a specific index
or filter out partitions or shards in your cloud data warehouse so you won't
be billed for scanning those. It can also be useful for constructing [links][ref-links].

<Warning>
  Heavy usage of `FILTER_PARAMS` is considered a bad practice. It usually
  leads to hard-to-maintain data models. Good rule of thumb is to use
  `FILTER_PARAMS` only for predicate pushdown performance optimizations.

  If you find yourself relying a lot on `FILTER_PARAMS`, it might mean that
  you need to rethink your approach to data modeling and potentially move
  some transformations upstream. Also, you might reconsider the choice of the
  data source.
</Warning>

`FILTER_PARAMS` has to be a top-level expression in `WHERE` and it has the
following syntax:

<CodeGroup>
  ```yaml title="YAML" theme={"dark"}
  cubes:
    - name: cube_name
      sql: |
        SELECT *
        FROM table
        WHERE {FILTER_PARAMS.cube_name.member_name.filter(sql_expression)}

      dimensions:
        - name: member_name
          # ...



  ```

  ```javascript title="JavaScript" theme={"dark"}
  cube(`cube_name`, {
    sql: `
      SELECT *
      FROM table
      WHERE ${FILTER_PARAMS.cube_name.member_name.filter(sql_expression)}
    `,

    dimensions: {
      member_name: {
        // ...
      }
    }
  })
  ```
</CodeGroup>

The `filter()` function accepts `sql_expression`, which could be either
a string or a function returning a string.

### Example with string

See the example below for the case when a string is passed to `filter()`:

<CodeGroup>
  ```yaml title="YAML" theme={"dark"}
  cubes:
    - name: order_facts
      sql: |
        SELECT *
        FROM orders
        WHERE {FILTER_PARAMS.order_facts.date.filter('date')}

      measures:
        - name: count
          type: count

      dimensions:
        - name: date
          sql: date
          type: time





  ```

  ```javascript title="JavaScript" theme={"dark"}
  cube(`order_facts`, {
    sql: `
      SELECT *
      FROM orders
      WHERE ${FILTER_PARAMS.order_facts.date.filter('date')}
    `,

    measures: {
      count: {
        type: `count`
      }
    },

    dimensions: {
      date: {
        sql: `date`,
        type: `time`
      }
    }
  })
  ```
</CodeGroup>

This will generate the following SQL...

```sql theme={"dark"}
SELECT COUNT(*) AS orders__count
FROM orders
WHERE
  date >= '2018-01-01 00:00:00' AND
  date <= '2018-12-31 23:59:59'
```

...for the `['2018-01-01', '2018-12-31']` date range passed for the
`order_facts.date` dimension as in following query:

```json theme={"dark"}
{
  "measures": ["order_facts.count"],
  "time_dimensions": [
    {
      "dimension": "order_facts.date",
      "dateRange": ["2018-01-01", "2018-12-31"]
    }
  ]
}
```

### Example with function

You can also pass a function as a `filter()` argument. This way, you can
add BigQuery shard filtering, which will reduce your billing cost.

<CodeGroup>
  ```yaml title="YAML" theme={"dark"}
  cubes:
    - name: events
      sql: |
        SELECT *
        FROM schema.`events*`
        WHERE {FILTER_PARAMS.events.date.filter(
          lambda x, y: f"""
            _TABLE_SUFFIX >= FORMAT_TIMESTAMP('%Y%m%d', TIMESTAMP({x})) AND
            _TABLE_SUFFIX <= FORMAT_TIMESTAMP('%Y%m%d', TIMESTAMP({y}))
          """
        )}

      dimensions:
        - name: date
          sql: date
          type: time
          


  ```

  ```javascript title="JavaScript" theme={"dark"}
  cube(`events`, {
    sql: `
      SELECT *
      FROM schema.\`events*\`
      WHERE ${FILTER_PARAMS.events.date.filter(
        (x, y) => `
          _TABLE_SUFFIX >= FORMAT_TIMESTAMP('%Y%m%d', TIMESTAMP(${x})) AND
          _TABLE_SUFFIX <= FORMAT_TIMESTAMP('%Y%m%d', TIMESTAMP(${y}))
        `
      )}
    `,

    dimensions: {
      date: {
        sql: `date`,
        type: `time`
      }
    }
  })
  ```
</CodeGroup>

<Info>
  When a function is passed to `filter()`, its arguments are passed as
  strings from the data source driver and it's your responsibility to handle
  type conversions in this case.
</Info>

In the example above, the filter on a time dimension accepts two values: the
lower and the upper bounds of a date range. If a filter accepts multiple values,
they are passed to the function as individual parameters:

```javascript theme={"dark"}
cube(`multi_filter`, {
  sql: `
    SELECT 123 AS value
    -- Multiple values: ${FILTER_PARAMS.multi_filter.dummy.filter(
      (...args) => JSON.stringify(args)
    )}
  `,

  dimensions: {
    dummy: {
      sql: `1`,
      type: `number`
    }
  }
})
```

### Example with segment

A `FILTER_PARAMS` argument can also name a [segment][ref-ref-segments]. A segment
is not compared to a value, so the expression passed to `filter()` is the whole
predicate rather than a column: it is rendered as-is when the query selects that
segment, and as `1 = 1` when it does not.

<CodeGroup>
  ```yaml title="YAML" theme={"dark"}
  cubes:
    - name: events
      sql: |
        SELECT *
        FROM events
        WHERE {FILTER_PARAMS.events.start_load.filter(
          "evid = 115 AND action_group = 'load'"
        )}

      segments:
        - name: start_load
          sql: "{CUBE}.evid = 115 AND {CUBE}.action_group = 'load'"
  ```

  ```javascript title="JavaScript" theme={"dark"}
  cube(`events`, {
    sql: `
      SELECT *
      FROM events
      WHERE ${FILTER_PARAMS.events.start_load.filter(
        `evid = 115 AND action_group = 'load'`
      )}
    `,

    segments: {
      start_load: {
        sql: `${CUBE}.evid = 115 AND ${CUBE}.action_group = 'load'`
      }
    }
  })
  ```
</CodeGroup>

The segment's own `sql` prefixes its columns with the cube, which is not in
scope inside the `sql` that builds that cube, so the pushed-down predicate is
restated in `filter()` — the same way a dimension's column is.

A function may be passed instead of a string, as long as it takes no arguments:
a segment carries no filter values to pass to it. A function that does take
arguments renders as `1 = 1`.

<Info>
  Naming a segment is only supported by the default SQL planner. With
  [`CUBEJS_TESSERACT_SQL_PLANNER`][ref-env-tesseract] set to `false`, such a
  binding always renders as `1 = 1`.
</Info>

### Time-shifted measures

A [`multi_stage` measure with a `time_shift`][ref-measures-time-shift] plans each
shift as its own query stage, and every stage re-reads the cube's `sql`. The
pushed-down column has to carry the same shift as the stage around it, otherwise
it filters the source rows by the unshifted range while the stage reads the
shifted one, and the stage comes back empty.

A shift declared as an `interval` is applied to the column for you. A shift
declared on a [calendar cube][ref-calendar-cubes] with its own `sql` cannot be:
it maps through a column on the calendar rather than offsetting the date, and
that column is not in scope inside the cube's `sql`. Passing the column as a
string in that case fails with a planning error naming the shift.

Pass a function instead. It receives the query's own filter values, so it can
widen the pushed-down range to cover the periods the shifted stages read.

`weekly_margin` joins a calendar cube whose `calendar_d` declares the
`prior_fiscal_year` shift, so the shifted stage reaches fact rows through that
mapping — which is why the band has to be widened by hand. The bracketing is
what carries across data sources; the date arithmetic is BigQuery syntax, so
adjust it for yours — here a fiscal prior year, bracketed at 371 and 364 days:

<CodeGroup>
  ```yaml title="YAML" theme={"dark"}
  cubes:
    - name: weekly_margin
      sql: |
        SELECT *
        FROM weekly_margin
        WHERE {FILTER_PARAMS.retail_calendar.calendar_d.filter(
          lambda x, y: f"""
            ((week_end_d >= DATE(TIMESTAMP({x}))
              AND week_end_d <= DATE(TIMESTAMP({y})))
             OR (week_end_d >= DATE_SUB(DATE(TIMESTAMP({x})), INTERVAL 371 DAY)
                 AND week_end_d <= DATE_SUB(DATE(TIMESTAMP({y})), INTERVAL 364 DAY)))
          """
        )}

      joins:
        - name: retail_calendar
          sql: "{CUBE}.week_end_d = {retail_calendar.calendar_d}"
          relationship: many_to_one
  ```

  ```javascript title="JavaScript" theme={"dark"}
  cube(`weekly_margin`, {
    sql: `
      SELECT *
      FROM weekly_margin
      WHERE ${FILTER_PARAMS.retail_calendar.calendar_d.filter(
        (x, y) => `
          ((week_end_d >= DATE(TIMESTAMP(${x}))
            AND week_end_d <= DATE(TIMESTAMP(${y})))
           OR (week_end_d >= DATE_SUB(DATE(TIMESTAMP(${x})), INTERVAL 371 DAY)
               AND week_end_d <= DATE_SUB(DATE(TIMESTAMP(${y})), INTERVAL 364 DAY)))
        `
      )}
    `,

    joins: {
      retail_calendar: {
        sql: `${CUBE}.week_end_d = ${retail_calendar.calendar_d}`,
        relationship: `many_to_one`
      }
    }
  })
  ```
</CodeGroup>

The values arrive as bare query parameters, so they are cast before use; and the
band is parenthesised as a whole because it is an `OR`, without which appending
another condition to the `WHERE` would bind it to the second branch alone.

Every stage then scans every band, so keep the bands as narrow as the calendar
actually requires.

## `FILTER_GROUP`

If you use `FILTER_PARAMS` in your query more than once, you must wrap them
with `FILTER_GROUP`.

<Warning>
  Otherwise, if you combine `FILTER_PARAMS` with any logical operators other than
  `AND` in SQL or if you use filters with [boolean operators][ref-filter-boolean]
  in your Cube queries, incorrect SQL might be generated.
</Warning>

`FILTER_GROUP` has to be a top-level expression in `WHERE` and it has the
following syntax:

<CodeGroup>
  ```yaml title="YAML" theme={"dark"}
  cubes:
    - name: cube_name
      sql: |
        SELECT *
        FROM table
        WHERE {FILTER_GROUP(
          FILTER_PARAMS.cube_name.member_name.filter(sql_expression),
          FILTER_PARAMS.cube_name.another_member_name.filter(sql_expression)
        )}

      dimensions:
        - name: member_name
          # ...

        - name: another_member_name
          # ...




  ```

  ```javascript title="JavaScript" theme={"dark"}
  cube(`cube_name`, {
    sql: `
      SELECT *
      FROM table
      WHERE ${FILTER_GROUP(
        FILTER_PARAMS.cube_name.member_name.filter(sql_expression),
        FILTER_PARAMS.cube_name.another_member_name.filter(sql_expression)
      )}
    `,

    dimensions: {
      member_name: {
        // ...
      },
      
      another_member_name: {
        // ...
      }
    }
  })
  ```
</CodeGroup>

### Example

To understand the value of `FILTER_GROUP`, consider the following data model
where two `FILTER_PARAMS` are combined in SQL using the `OR` operator:

<CodeGroup>
  ```yaml title="YAML" theme={"dark"}
  cubes:
    - name: filter_group
      sql: |
        SELECT *
          FROM (
            SELECT 1 AS a, 3 AS b UNION ALL
            SELECT 2 AS a, 2 AS b UNION ALL
            SELECT 3 AS a, 1 AS b
          ) AS data
          WHERE
            {FILTER_PARAMS.filter_group.a.filter("a")} OR
            {FILTER_PARAMS.filter_group.b.filter("b")}

      dimensions:
        - name: a
          sql: a
          type: number

        - name: b
          sql: b
          type: number




  ```

  ```javascript title="JavaScript" theme={"dark"}
  cube(`filter_group`, {
    sql: `
      SELECT *
        FROM (
          SELECT 1 AS a, 3 AS b UNION ALL
          SELECT 2 AS a, 2 AS b UNION ALL
          SELECT 3 AS a, 1 AS b
        ) AS data
        WHERE
          ${FILTER_PARAMS.filter_group.a.filter('a')} OR
          ${FILTER_PARAMS.filter_group.b.filter('b')}
    `,

    dimensions: {
      a: {
        sql: `a`,
        type: `number`
      },

      b: {
        sql: `b`,
        type: `number`
      }
    }
  })
  ```
</CodeGroup>

If the following query is run...

```json theme={"dark"}
{
  "dimensions": [
    "filter_group.a",
    "filter_group.b"
  ],
  "filters": [
    {
      "member": "filter_group.a",
      "operator": "gt",
      "values": ["1"]
    },
    {
      "member": "filter_group.b",
      "operator": "gt",
      "values": ["1"]
    }
  ]
}
```

...the following (logically incorrect) SQL will be generated:

```sql theme={"dark"}
SELECT
  "filter_group".a,
  "filter_group".b
FROM (
  SELECT *
  FROM (
    SELECT 1 AS a, 3 AS b UNION ALL
    SELECT 2 AS a, 2 AS b UNION ALL
    SELECT 3 AS a, 1 AS b
  ) AS data
  WHERE
    (a > 1) OR  -- Incorrect logical operator here
    (b > 1)
) AS "filter_group"
WHERE
  "filter_group".a > 1 AND
  "filter_group".b > 1
GROUP BY 1, 2
```

As you can see, since an array of filters has `AND` semantics, Cube has
correctly used the `AND` operator in the "outer" `WHERE`. At the same time,
the hardcoded `OR` operator has propagated to the "inner" `WHERE`, leading to
a logically incorrect query.

Now, if the cube is defined the following way...

<CodeGroup>
  ```yaml title="YAML" theme={"dark"}
  cubes:
    - name: filter_group
      sql: |
        SELECT *
          FROM (
            SELECT 1 AS a, 3 AS b UNION ALL
            SELECT 2 AS a, 2 AS b UNION ALL
            SELECT 3 AS a, 1 AS b
          ) AS data
          WHERE
            {FILTER_GROUP(
              FILTER_PARAMS.filter_group.a.filter("a"),
              FILTER_PARAMS.filter_group.b.filter("b")
            )}

      # ...
  ```

  ```javascript title="JavaScript" theme={"dark"}
  cube(`filter_group`, {
    sql: `
      SELECT *
        FROM (
          SELECT 1 AS a, 3 AS b UNION ALL
          SELECT 2 AS a, 2 AS b UNION ALL
          SELECT 3 AS a, 1 AS b
        ) AS data
        WHERE
          ${FILTER_GROUP(
            FILTER_PARAMS.filter_group.a.filter('a'),
            FILTER_PARAMS.filter_group.b.filter('b')
          )}
    `,

    // ...
  ```
</CodeGroup>

...the following correct SQL will be generated for the same query:

```sql theme={"dark"}
SELECT
  "filter_group".a,
  "filter_group".b
FROM (
  SELECT *
  FROM (
    SELECT 1 AS a, 3 AS b UNION ALL
    SELECT 2 AS a, 2 AS b UNION ALL
    SELECT 3 AS a, 1 AS b
  ) AS data
  WHERE
    (a > 1) AND  -- Correct logical operator here
    (b > 1)
) AS "filter_group"
WHERE
  "filter_group".a > 1 AND
  "filter_group".b > 1
GROUP BY 1, 2
```

You can also use [boolean operators][ref-filter-boolean] in the Cube query
to express more complex filtering logic:

```json theme={"dark"}
{
  "dimensions": [
    "filter_group.a",
    "filter_group.b"
  ],
  "filters": [
    {
      "or": [
        {
          "member": "filter_group.a",
          "operator": "gt",
          "values": ["1"]
        },
        {
          "member": "filter_group.b",
          "operator": "gt",
          "values": ["1"]
        }
      ]
    }
  ]
}
```

With `FILTER_GROUP`, the following correct SQL will be generated:

```sql theme={"dark"}
SELECT
  "filter_group".a,
  "filter_group".b
FROM (
  SELECT *
  FROM (
    SELECT 1 AS a, 3 AS b UNION ALL
    SELECT 2 AS a, 2 AS b UNION ALL
    SELECT 3 AS a, 1 AS b
  ) AS data
  WHERE
    (a > 1) OR
    (b > 1)
) AS "filter_group"
WHERE
  "filter_group".a > 1 OR
  "filter_group".b > 1
GROUP BY 1, 2
```

## `SQL_UTILS`

### `convertTz`

In case you need to convert your timestamp to user request timezone in cube or
member SQL you can use `SQL_UTILS.convertTz()` method. Note that Cube will
automatically convert timezones for `timeDimensions` fields in
[queries](/reference/core-data-apis/rest-api/query-format#query-properties).

<Warning>
  Dimensions that use `SQL_UTILS.convertTz()` should not be used as
  `timeDimensions` in queries. Doing so will apply the conversion multiple times
  and yield wrong results.
</Warning>

In case the same database field needs to be queried in `dimensions` and
`timeDimensions`, create dedicated dimensions in the cube definition for the
respective use:

<CodeGroup>
  ```yaml title="YAML" theme={"dark"}
  cubes:
    - name: visitors
      # ...

      dimensions:
        # Do not use in timeDimensions query property
        - name: created_at_converted
          sql: "{SQL_UTILS.convertTz(`created_at`)}"
          type: time

        # Use in timeDimensions query property
        - name: created_at
          sql: created_at
          type: time



  ```

  ```javascript title="JavaScript" theme={"dark"}
  cube(`visitors`, {
    // ...

    dimensions: {
      // Do not use in timeDimensions query property
      created_at_converted: {
        sql: SQL_UTILS.convertTz(`created_at`),
        type: `time`
      },

      // Use in timeDimensions query property
      created_at: {
        sql: `created_at`,
        type: "time"
      }
    }
  })
  ```
</CodeGroup>

## `COMPILE_CONTEXT`

<Warning>
  `COMPILE_CONTEXT` is evaluated only once per each key generated by `context_to_app_id`.
  The `securityContext` defined in `COMPILE_CONTEXT` doesn't change
  its value for different users, however, it will change for
  different tenants as defined in `context_to_app_id`.
</Warning>

A global `COMPILE_CONTEXT` contains `securityContext` and any other variables provided by
[`extendContext`][ref-config-ext-ctx].

Use [Jinja][ref-dynamic-jinja] `{{ }}` syntax to access `COMPILE_CONTEXT` variable.

<CodeGroup>
  ```yaml title="YAML" theme={"dark"}
  cubes:
    - name: users
      sql_table: "user_{{ COMPILE_CONTEXT.securityContext.deployment_id }}.users"
  ```

  ```javascript title="JavaScript" theme={"dark"}
  cube(`users`, {
    sql_table: `user_${COMPILE_CONTEXT.securityContext.deployment_id}.users`
  })
  ```
</CodeGroup>

## `SECURITY_CONTEXT`

<Warning>
  **`SECURITY_CONTEXT` is deprecated and can be removed without further notice.**
  Use [`query_rewrite`][ref-config-queryrewrite] instead.
</Warning>

`SECURITY_CONTEXT` global variable holds a security context that is passed to Cube via API.
Please read the [Security Context page][ref-sec-ctx] for more information on how
to provide security context to Cube.

```javascript theme={"dark"}
cube(`orders`, {
  sql: `
    SELECT *
    FROM orders
    WHERE ${SECURITY_CONTEXT.email.filter("email")}
  `,

  dimensions: {
    date: {
      sql: `date`,
      type: `time`
    }
  }
})
```

To ensure filter value presents for all requests `requiredFilter` can be used:

```javascript theme={"dark"}
cube(`orders`, {
  sql: `
    SELECT *
    FROM orders
    WHERE ${SECURITY_CONTEXT.email.requiredFilter("email")}
  `,

  dimensions: {
    date: {
      sql: `date`,
      type: `time`
    }
  }
})
```

You can access values of context variables directly in JavaScript in order to
use it during your SQL generation. For example:

<Warning>
  Use of this feature entails SQL injection security risk. Use it with caution.
</Warning>

```javascript theme={"dark"}
cube(`orders`, {
  sql: `
    SELECT *
    FROM ${
      SECURITY_CONTEXT.type.unsafeValue() === "employee" ? "employee" : "public"
    }.orders
  `,

  dimensions: {
    date: {
      sql: `date`,
      type: `time`
    }
  }
})
```

[ref-config-ext-ctx]: /reference/configuration/config#extendcontext

[ref-config-queryrewrite]: /reference/configuration/config#query_rewrite

[ref-sec-ctx]: /docs/data-modeling/access-control/context

[ref-ref-cubes]: /reference/data-modeling/cube

[ref-syntax-references]: /docs/data-modeling/concepts/syntax#references

[ref-dynamic-data-models]: /docs/data-modeling/dynamic/jinja

[ref-query-filter]: /reference/core-data-apis/rest-api/query-format#query-properties

[ref-dynamic-jinja]: /docs/data-modeling/dynamic/jinja

[ref-filter-boolean]: /reference/core-data-apis/rest-api/query-format#boolean-logical-operators

[ref-links]: /reference/data-modeling/dimensions#links

[ref-ref-segments]: /reference/data-modeling/segments

[ref-env-tesseract]: /reference/configuration/environment-variables#cubejs_tesseract_sql_planner

[ref-measures-time-shift]: /reference/data-modeling/measures#time_shift

[ref-calendar-cubes]: /docs/data-modeling/concepts/calendar-cubes