Left outer Join in Cube JS

Question

I have a requirement to join two tables in cube js. First table has list of customers. Second table has list orders with customer ids with purchase date. I have to display the total orders placed by the customer with date filter. If the there is an order available in the second table I am able to get the customer row with the order count. If the date range is changed and if no orders available it should show zero against customer, instead no row is returned.

Any work around or help on this is much appreciated.

score 0 · Answer 1 · edited Sep 08 '21 at 09:52

you could specify the left outer join in the SQL property of the cube:

cube(`Users`, {
  sql: `SELECT * from orders LEFT OUTER JOIN customers,
})

In your dimensions specify a new dimension that takes 1 if order id is not null and takes 0 else, as follows:

hasOrder: {
            type: `number`,
            case: {
                when: [
                    { sql: `${CUBE}.order_id is null`, label: { sql: Number("0") } },
                    { sql: `${CUBE}.order_id is not null`, label: { sql: Number("1") } } 
                ],
            }
        },

then define a measure that sums the number of Orders, it should return O for customers with no orders:

measures: {
    ordersCompletedCount: {
      type: `sum`,
      sql: `${CUBE.hasOrder}`
    },
  },

Left outer Join in Cube JS

1 Answers1