How to add OR condition in Cube.js filter query

Question

I'm want to add OR condition in the JSON query of Cube.js. But once I added one more condition in the filter it always adds AND condition in SQL query.

Below is the JSON query that I'm trying.

{
  "dimensions": [
    "Employee.name",
    "Employee.company"
  ],
  "timeDimensions": [],
  "measures": [],
  "filters": [
    {
      "dimension": "Employee.company",
      "operator": "contains",
      "values": [
        "soft"
      ]
    },
    {
      "dimension": "Employee.name",
      "operator": "contains",
      "values": [
        "soft"
      ]
    }
  ]
}

It generates below SQL query.

SELECT
  `employee`.name `employee__name`,
  `employee`.company `employee__company`
FROM
  DEMO.Employee AS `employee`
WHERE
  `employee`.company LIKE CONCAT('%', 'soft', '%') 
             AND 
  `employee`.name LIKE CONCAT('%', 'soft', '%')
GROUP BY
  1,
  2;

What is the JSON query for Cube.js if I want to generate below SQL

SELECT
  `employee`.name `employee__name`,
  `employee`.company `employee__company`
FROM
  DEMO.Employee AS `employee`
WHERE
  `employee`.company LIKE CONCAT('%', 'soft', '%') 
             OR
  `employee`.name LIKE CONCAT('%', 'soft', '%')
GROUP BY
  1,
  2;

score 1 · Answer 1 · answered Oct 26 '19 at 18:50

1

API support for logical operators isn't shipped yet. Meanwhile there're several workarounds:

Define dimension that mimics OR behavior. In your case it's

cube(`Employee`, {
 // ...

 dimensions: {
   companyAndName: {
     sql: `CONCAT(${company}, ' ', ${name})`,
     type: `string`
   }
 }
});

Define segments. Those can be also generated: https://cube.dev/docs/schema-generation

cube(`Employee`, {
  // ...

  segments: {
    soft: {
      sql: `${company} LIKE CONCAT('%', 'soft', '%') OR ${name} LIKE CONCAT('%', 'soft', '%')` 
    }
  }
});

answered Oct 26 '19 at 18:50

Pavel Tiunov

1,163
6
8

Thanks! this workaround works for the above scenario. but suppose I want to perform the same scenario for multiple dimensions then I will need to **CONCAT** those dimensions. – Harshal Nathe Nov 05 '19 at 08:41
Yep. Right. You should use `CONCAT()` in case you want mimic `OR` behavior for contains operator. – Pavel Tiunov Nov 05 '19 at 21:38
1

I've created a github issue to support this more natively without jumping through hoops with a concat https://github.com/cube-js/cube.js/issues/259 – ferrants Nov 10 '19 at 23:14

Kaizendae · Answer 2 · 2021-09-09T09:06:35.660

this is how you do it

{
      or: [
        {
           "dimension": "Employee.company",
           "operator": "contains",
           "values": [
              "soft"
              ]
        },
        {
           "dimension": "Employee.name",
           "operator": "contains",
           "values": [
             "soft"
             ]
         }
       
      ];
    }

Refer to this Boolean logical operators section in the documentation

How to add OR condition in Cube.js filter query

2 Answers2

Linked