[Feature Request] new cube_sql property to enhance data blending

[ZiggiZagga]

I propose adding a cube_sql property to enhance data blending in Cube.js. This feature would allow developers to reference other cubes directly by their identifiers within SQL queries, simplifying syntax and improving readability. Currently, referencing another cube's SQL requires explicit calls to .sql(), adding complexity. The cube_sql property would abstract these details, making cube references more intuitive and streamlining data modeling processes. This could encourage clearer code and ease the learning curve for new users.

[igorlukanin]

Hi @ZiggiZagga 👋 Thanks for your proposal.

It would be really great if you can elaborate on your proposal: add a data model code example that demonstrated the use of such a feature. Also, I'm very curious to compare that with the "explicit calls to .sql()" approach.

[ZiggiZagga]

Hi @igorlukanin ,

Thanks for your reply.

Problem Statement

In a scenario, where I am testing dynamic data modeling, all cube definitions are fetched from a REST API and assembled using asyncModule(). In the example I tried using FROM (${online_orders.sql()}) lead to errors. The use of .sql() didn't work for me.

Example:

cube(`all_sales`, {

  sql: `

    SELECT

      amount,

      user_id AS customer_id,

      created_at,

      'online' AS row_type

    FROM (${online_orders.sql()}) AS online

    UNION ALL

    SELECT

      amount,

      customer_id,

      created_at,

      'retail' AS row_type

    FROM (${retail_orders.sql()}) AS retail

`});

As a workaround I rewrote my api to respond with the full sql queries to the undelying database server. This works really fine. So my next steps is to test cubes authorization features on this.

I really like the way cube helps design data definitions in one place, this simplifies the whole data accessing structure.

The cube_sql property/function is just a first feedback, an idea. I am not sure if it makes any sense.

cube(`all_sales`, {

  sql: `

    SELECT

      amount,

      user_id AS customer_id,

      created_at,

      'online' AS row_type

    FROM cube_sql(online_orders) AS online

    UNION ALL

    SELECT

      amount,

      customer_id,

      created_at,

      'retail' AS row_type

    FROM cube_sql(retail_orders) AS retail

`});