How to achieve an indirect join in Cube.js?

Question

I might misunderstand cube.js joins, but in SQL I can do a join from a table removed by one like so:

SELECT * FROM a
LEFT JOIN b ON a.name = b.name
LEFT JOIN c ON b.state = c.state;

My question is: how do I achieve this in cube.js?

I tried to recreate above with the following schema (and some variations on it):

cube(`A`, {
  sql: `SELECT * FROM a`,

  joins: {
    B: {
      sql: `${A}.name = ${B}.name`,
      relationship: `belongsTo`,
    },
    C: {
      sql: `${B}.state = ${C}.state`,
      relationship: `belongsTo`,
    },
  },

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

    name: {
      sql: `${B}.name`,
      type: `string`,
    },

    state: {
      sql: `${C}.state`,
      type: `string`,
    },
  },
});

where cubes B and C are also defined including the dimensions used here.

However, formulating the following query:

dimensions: [`A.state`];

throws the error:

Error: ER_BAD_FIELD_ERROR: Unknown column 'b.state' in 'on clause'

I might well miss the obvious, and would be grateful for any pointers...

Answer 1

It turns out that the following B to C join:

  joins: {

    //...

    C: {
      sql: `${B}.state = ${C}.state`,
      relationship: `belongsTo`,
    },
  },

needs to happen within cube B not cube A. The reason seems to be that cubes reference other cubes rather than building persistent tables under the hood.

This also explains why dimensions that originate from joined tables still need to point to the cubes representing the joined table - like for example the state dimension in the question's code above:

state: {
  sql: `${C}.state`,
  type: `string`,
},