Unable to reference static cube names (inside schema folder) in the dynamic schema while creating joins

[manickaws]

Description: I have static cubes inside the schema folder (eg: Users). I also create dynamic schema fetching from database. The dynamic schema works perfectly. However when i try to join the dynamic cubes and the static cubes , i get reference error "Users is not defined"

To Reproduce: Create a static cube inside schema folder. Fetch the data from Database and create dynamic schema. Now inside the dynamic schema try to create a relationship with the static cube using "belongs to". In the playground when you click the "dimensions" button, you will get a reference error.

Screenshot of the code as below :

Screenshot of the error as below :

Version Used : 0.22.3

[rongfengliang]

@manickaws current you can use sqlAlias when dynamic schema generate include join. so join sql can use name directly and needn't using reference. can do like this

const { transformDimensions, transformJoins, transformMeasures, convertStringPropToFunction } = require("./utils")
asyncModule(async () => {
  const dynamicCubes = [
    {
    "name": "payments",
    "sqlAlias": `payments`,
     "sql": "SELECT * FROM public.\"payments\"",
     "dataSource": "default",
     "joins": {
       "demoapp": {
         "relationship": `belongsTo`,
         "sql": "payments.__id = demoapp.__id"
       }
     },
     "measures": {
       "count": {
         "sql": "COUNT(DISTINCT \"payments\".__id)",
         "type": "number"
       },
       "total": {
         "sql": "\"payments\".\"total\"",
         "type": "sum"
       }
     },
     "dimensions": {
       "__id": {
         "sql": "__id",
         "type": "string",
         "primaryKey": true
       },
       "name": {
         "sql": "\"payments\".\"name\"",
         "type": "string"
       }
     }
   }, 
   {
      "name": "demoapp",
      "sqlAlias": `demoapp`,
      "sql": "SELECT * FROM public.\"demoapp\"",
     "dataSource": "default",
     "joins": {
       "demoapp": {
         "relationship": `belongsTo`,
         "sql": "payments.__id = demoapp.__id"
       }
     },
     "measures": {
       "count": {
         "sql": "COUNT(DISTINCT \"payments\".__id)",
         "type": "number"
       }
     },
     "dimensions": {
       "__id": {
         "sql": "__id",
         "type": "string",
         "primaryKey": true
       },
       "name": {
         "sql": "\"payments\".\"name\"",
         "type": "string"
       }
     }
 }
 ]
  dynamicCubes.forEach((dynamicCube) => {
    const dimensions = transformDimensions(dynamicCube.dimensions);
    const measures = transformMeasures(dynamicCube.measures);
    const joins = transformJoins(dynamicCube.joins);
    cube(dynamicCube.name, {
      sql: dynamicCube.sql,
      dimensions,
      measures,
      joins
    });
  });
}
)

[hassankhan]

Hi @manickaws, just wanted to follow up and see if @rongfengliang's suggestion worked for you?

[diogosilva30]

how to do this in a view created with asyncModule?

Example:

asyncModule(async () => {
  // Create the objects for the view
  const viewObject = {
    title: i18n.gettext(`[Traffic Vertical]`),
    cubes: [
      // Calendar is the cube that "glues" all together
      {
        join_path: calendar,
        includes: "*",
        prefix: true,
      },
      // Counters
      {
        join_path: calendar.traffic_counter,
        includes: "*",
        prefix: true,
      },
   };

   if (someFunc("some_arg")) {
    view(`traffic`, viewObject);   

});

I'm dynamically creating views and cubes based on someFunc. However, this fails with:

ReferenceError: calendar is not defined
    at views/traffic.js:16:18

Any help?

[paveltiunov]

@diogosilva30 You can use strings in join_path instead. Like join_path: 'calendar.traffic_counter'

[igorlukanin]

Hi @manickaws 👋

I have just tried the following data model:

cube(`A`, {
  sql: `SELECT 123 AS value`,

  dimensions: {
    value: {
      sql: `value`,
      type: `number`,
      primary_key: true
    }
  }
})

asyncModule(async () => {
  cube(`B`, {
    sql: `SELECT 123 AS value`,

    joins: {
      A: {
        relationship: `one_to_one`,
        sql: `${CUBE.value} = ${A.value}`
      }
    },

    dimensions: {
      value: {
        sql: `value`,
        type: `number`,
        primary_key: true
      }
    }
  })
})

It generates correct SQL:

So, here's how you can generate joins dynamically. Does this answer your question?