Enforcing mandatory filters

Use case

Let's imagine that on New Year's Eve, December 30th, 2019, we renamed our store, changed the design, and started selling completely different products. At the same time, we decided to reuse the database for the new store. So, we'd like to only show orders created after December 30th, 2019. In the recipe below, we'll learn how to add mandatory filters to all queries.

Configuration

To enforce mandatory filters we'll use the queryRewrite parameter in the cube.js configuration file.

To solve this, we add a filter that will apply to all queries. This will make sure we only show orders created after December 30th, 2019.

module.exports = {
  queryRewrite: (query) => {
    query.filters.push({
      member: `orders.created_at`,
      operator: "afterDate",
      values: ["2019-12-30"],
    });
 
    return query;
  },
};

Query

To get the orders we will send two queries with filters by status:

# Completed orders
curl cube:4000/cubejs-api/v1/load \
  -H "Authorization: eeyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoib3BlcmF0b3IiLCJpYXQiOjE2Mjg3NDUwNDUsImV4cCI6MTgwMTU0NTA0NX0.VErb2t7Bc43ryRwaOiEgXuU5KiolCT-69eI_i2pRq4o" \
  'query={"measures": [], "order": [["Users.created_at", "asc"]], "dimensions": ["orders.number", "orders.created_at"],
    "filters": [
      {
        "member": "orders.status",
        "operator": "equals",
        "values": ["completed"]
      }
    ],
    "limit": 5
  }'

# Shipped orders
curl cube:4000/cubejs-api/v1/load \
  -H "Authorization: eeyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoib3BlcmF0b3IiLCJpYXQiOjE2Mjg3NDUwNDUsImV4cCI6MTgwMTU0NTA0NX0.VErb2t7Bc43ryRwaOiEgXuU5KiolCT-69eI_i2pRq4o" \
  'query={"measures": [], "order": [["orders.created_at", "asc"]], "dimensions": ["orders.number", "orders.created_at"],
    "filters": [
      {
        "member": "orders.status",
        "operator": "equals",
        "values": ["shipped"]
      }
    ],
    "limit": 5
  }'

Result

We have received orders created after December 30th, 2019.

Completed orders:

[
  {
    "orders.number": 78,
    "orders.created_at": "2020-01-01T00:00:00.000",
  },
  {
    "orders.number": 43,
    "orders.created_at": "2020-01-02T00:00:00.000",
  },
  {
    "orders.number": 87,
    "orders.created_at": "2020-01-04T00:00:00.000",
  },
  {
    "orders.number": 45,
    "orders.created_at": "2020-01-04T00:00:00.000",
  },
  {
    "orders.number": 28,
    "orders.created_at": "2020-01-05T00:00:00.000",
  },
];

Shipped orders:

[
  {
    "orders.number": 57,
    "orders.created_at": "2019-12-31T00:00:00.000",
  },
  {
    "orders.number": 38,
    "orders.created_at": "2020-01-01T00:00:00.000",
  },
  {
    "orders.number": 10,
    "orders.created_at": "2020-01-02T00:00:00.000",
  },
  {
    "orders.number": 19,
    "orders.created_at": "2020-01-02T00:00:00.000",
  },
  {
    "orders.number": 15,
    "orders.created_at": "2020-01-02T00:00:00.000",
  },
];

Source code

Please feel free to check out the full source code (opens in a new tab) or run it with the docker-compose up command. You'll see the result, including queried data, in the console.

Implementing Funnel Analysis Enforcing column-based access