Unable to make join between dynamic cubes
- 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**

**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
Assessment
This issue has not been assessed yet.