How to pass dynamic values in sql in cube.js schema?

Question

Somewhere I want status=2 or status<3. Instead of writing separate schemas, how to reuse by passing dynamic values to the status field in SQL?

cube(`OrderFacts`, {
  sql: `SELECT * FROM orders WHERE status>3`, // <--- I want to pass dynamic values to the condition

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

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

score 1 · Answer 1 · answered May 18 '21 at 14:08

Please refer to the Unsafe Value section of the Context Variables documentation:

cube(`Orders`, {
  sql: `SELECT * FROM orders WHERE status > ${SECURITY_CONTEXT.status.unsafeValue()`,
});

Generally speaking, it's best not to do this though; instead I'd recommend using segments to achieve the same functionality (assuming there aren't a lot of values for status)

How to pass dynamic values in sql in cube.js schema?

1 Answers1