igorlukanin
commented
2 months ago Hi @MadhusudanN 👋
This is a typical transitive join pitfall: https://cube.dev/docs/product/data-modeling/concepts/working-with-joins#transitive-join-pitfalls. You can solve it by moving joins in the cubes, please see the explanation on the docs page.
Usecase :: We have two tables which do not have any foreign key association, there is third table which joins them.To answer questions involving these two tables, cube needs to pick the third table and answer the questions.
Tables Movies -- Details about movies released (First table) Theater -- Details about the theaters in a city (Second table) Movies_To_Theates -- Connecting table (Third table)
Cube Model :: Movies cube which has list of all movies that are released. Theater cube which has all details about theaters in a city. There is no direct association through foreign key for between the above cubes. There is a third cube Movies_to_Theaters which has association with both of the above cubes through foreign keys (Movie_ID and Threater_ID respectively.)
Question :: "Give me all the movies that are played in a given theater" To answer above question, currently cube looks for Movies and Theater and finds that there is no join between them and fails Can you please let us know how we can model, so that cube picks up the third table which connects both of these tables and gives the meaningful query response.