cube-js / cube-js/cube

Unable to make join between dynamic cubes

Open
#6,145 1 comment 9 reactions 0 assignees View on GitHub
question
Dominant language
Rust
Stars
20.8k
Forks
2.1k
Avg merge
1d 2h
Merged PRs (30d)
181

Description

**Describe the bug**
I have 3 cubes: 1 static cube and 2 dynamic cubes. When I try to make join between dynamic cube and static it works well, but when I try to make join between 2 dynamic cubes, it starts to generate wrong SQL.

**To Reproduce**
Steps to reproduce the behavior:
1. Create dynamic schema
2. Create a relationship between cubes using `belongs to` and `hasMany`

Cases when LEFT JOIN is absent:
- Try to choose measure from `DynamicPatient` cube and dimensions from `DynamicPatient` and `DynamicCondition`.
- Try to choose measure from `DynamicPatient` cube and dimensions from `DynamicPatient` and `DynamicCondition` and you can also add filter by `dimension` from `DynamicCondition` cube
- Hit the "run" button and get error that can't find column from `DynamicCondition`, because the is no left join
- In SQL tab I can't see LEFT JOIN from `DynamicPatient` cube to `DynamicCondition`.

Cases when LEFT JOIN is present:
- Try to choose measure from `DynamicPatient` and `DynamicCondition` cube and dimensions from `DynamicPatient` and `DynamicCondition`.
- Try to choose measure from `DynamicPatient` cube and dimensions from `DynamicPatient` and `DynamicCondition` and add filter by `measure` from `DynamicCondition` cube
4. No errors - LEFT JOIN is present.

**Expected behavior**
When I choose measure from `DynamicPatient` cube and dimensions from `DynamicPatient` and `DynamicCondition` - No error with proper LEFT JOIN

**Screenshots**
Знімок екрана 2023-02-10 о 13 43 44
Знімок екрана 2023-02-10 о 13 44 12
Знімок екрана 2023-02-10 о 13 44 48
Знімок екрана 2023-02-10 о 13 45 03

**Minimally reproducible Cube Schema**
```javascript
const { transformDimensions, transformMeasures, transformJoins } = require("../utils");

asyncModule(async () => {
const dynamicCubes = [
{
title: `DynamicPatient`,
sqlAlias: `dynamic_patient`,
sql: `SELECT * FROM ehr."Patient" where tenant = 1`,
measures: {
count: {
type: `count`
}
},
joins: {
DynamicCondition: {
relationship: `hasMany`,
sql: `dynamic_patient.id = dynamic_condition.subject_reference_id`
}
},
dimensions: {
id: {
sql: `id`,
type: `string`,
primaryKey: true
},
gender: {
sql: `gender`,
type: `string`
}
}
},
{
title: `DynamicCondition`,
sqlAlias: `dynamic_condition`,
sql: `SELECT * FROM ehr."Condition" where tenant = 1`,

measures: {
count: {
type: `count`
}
},

joins: {
DynamicPatient: {
relationship: `belongsTo`,
sql: `dynamic_condition.subject_reference_id = dynamic_patient.id`
}
},

dimensions: {
id: {
sql: `id`,
type: `string`,
primaryKey: true
},
subjectReferenceId: {
sql: `subject_reference_id`,
type: `string`,
shown: true
}
}
}
];

dynamicCubes.forEach((dynamicCube) => {
cube(dynamicCube.title, {
sql: dynamicCube.sql,
dimensions: transformDimensions(dynamicCube.dimensions),
measures: transformMeasures(dynamicCube.measures),
joins: transformJoins(dynamicCube.joins),
sqlAlias: dynamicCube.sqlAlias,
refreshKey: {
every: `1 day`
}
});
});
});
```

**Version:**
0.31.59

**Additional context**
Clickhouse DB

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.