Outer join using Cube.js

Question

I am using Cube.js for fetching the data from Postgres. By default, cube.js does a left join on 2 selected tables. Is there any way to get the result by Full Outer Join?

User Register Table
ID TIME
1 10.00
3 9.00

Last Active Table
ID TIME
1 11.00
2 10.00

So output I want is
ID Last_active_time Register_time
1 11.00 10.00
2 10.00 ----
3 ---- 9.00

Could you please provide what data structure you have and what's your final result you want to get? — Pavel Tiunov, Oct 30 '19 at 20:48

score 3 · Accepted Answer · edited Oct 16 '20 at 06:38

Cube.js uses only LEFT JOIN to express relationship between cubes for the purpose of encouraging right Cube.js schema design: https://cube.dev/docs/joins. Your case can be expressed as following Cube.js schema:

cube(`Users`, {
  sql: `
  SELECT DISTINCT id FROM users_register 
  UNION 
  SELECT DISTINCT id FROM last_active`,

  joins: {
    UsersRegister: {
      sql: `${Users}.id = ${UsersRegister}.id`,
      relationship: `hasMany`
    },
    UsersLastActive: {
      sql: `${Users}.id = ${UsersLastActive}.id`,
      relationship: `hasMany`
    }
  },

  dimensions: {
    id: {
      sql: `id`,
      type: `number`,
      primaryKey: true
    }
  }
});

cube(`UsersRegister`, {
  sql: `select * from users_register`,

  measures: {
    registerTime: {
      sql: `time`,
      type: `min`
    }
  },

  dimensions: {
    id: {
      sql: `id`, // if id is unique within users_register
      type: `number`,
      primaryKey: true
    }
  }
});

cube(`UsersLastActive`, {
  sql: `select * from last_active`,

  measures: {
    lastActiveTime: {
      sql: `time`,
      type: `max`
    }
  },

  dimensions: {
    id: {
      sql: `id`, // if id is unique within last_active
      type: `number`,
      primaryKey: true
    }
  }
});

Query to get desired result:

{
  measures: ['UsersLastActive.lastActiveTime', 'UsersRegister.registerTime'],
  dimensions: ['Users.id']
}

Outer join using Cube.js

1 Answers1