Join the same cube twice in one view

[oxoxlol]

Problem

I've asked this question on stackoverflow (https://stackoverflow.com/questions/79044418/transitive-join-to-an-entity-referenced-by-several-other-entities) before, but didn't get an answer. I decided to duplicate the question here.

I have this schema

I build a view based on the model described above:

views:
   - name: view_tpch
     public: true
   
     cubes:
         - join_path: tpch_lineitem
           includes:
              - L_SUPPKEY

         - join_path: tpch_orders
           includes:
               - O_CUSTKEY

         - join_path: tpch_supplier
           includes: 
               - S_SUPPKEY
               - S_NAME
               - S_NATIONKEY

         - join_path: tpch_customer
           includes: 
               - C_CUSTKEY
               - C_NAME
               - C_NATIONKEY

         - join_path: tpch_lineitem.tpch_partsupp.tpch_supplier.tpch_nation
           alias: supp_nation
           prefix: true
           includes:
              - name: N_NATIONKEY
                alias: supp_N_NATIONKEY
              - name: N_NAME
                alias: supp_N_NAME

         - join_path: tpch_lineitem.tpch_orders.tpch_customer.tpch_nation
           alias: cust_nation
           prefix: true
           includes:
              - name: N_NATIONKEY
                alias: cust_N_NATIONKEY
              - name: N_NAME
                alias: cust_N_NAME

The problem is that when cube nation references 2 cubes:

1. customer
2. supplier

In the view I want to display the supplier nation and customer nation at the same time.

However, for some reason, the nation cube is always jojointed only once. And always only for the first join_path. The direction of the joins is done using “dot notation”, but this only helps to select the correct path for the first join. The second one is ignored.

I thought that these issues would help me somehow in my question, but they did not:

1. How to influence the join path in a query? #438
2. Cube Ignores a view defined join path under a certain scenario #8499

Please suggest how I can solve my problem.

Related Cube.js generated SQL

SELECT
"tpch_lineitem".L_SUPPKEY "view_tpch___l__s_u_p_p_k_e_y", 
"tpch_orders".O_CUSTKEY "view_tpch___o__c_u_s_t_k_e_y", 
"tpch_supplier".S_SUPPKEY "view_tpch___s__s_u_p_p_k_e_y", 
"tpch_supplier".S_NAME "view_tpch___s__n_a_m_e", 
"tpch_supplier".S_NATIONKEY "view_tpch___s__n_a_t_i_o_n_k_e_y", 
"tpch_customer".C_CUSTKEY "view_tpch___c__c_u_s_t_k_e_y", 
"tpch_customer".C_NAME "view_tpch___c__n_a_m_e", 
"tpch_customer".C_NATIONKEY "view_tpch___c__n_a_t_i_o_n_k_e_y", 
"tpch_nation".N_NATIONKEY "view_tpch__supp_nation_supp__n__n_a_t_i_o_n_k_e_y", 
"tpch_nation".N_NAME "view_tpch__supp_nation_supp__n__n_a_m_e", 
"tpch_nation".N_NATIONKEY "view_tpch__cust_nation_cust__n__n_a_t_i_o_n_k_e_y", 
"tpch_nation".N_NAME "view_tpch__cust_nation_cust__n__n_a_m_e"
    FROM
      (select 
     L_ORDERKEY
   , L_PARTKEY
   , L_SUPPKEY
   , L_LINENUMBER
   , L_QUANTITY
   , L_EXTENDEDPRICE
   , L_DISCOUNT
   , L_TAX
   , L_RETURNFLAG
   , L_LINESTATUS
   , L_SHIPDATE
   , L_COMMITDATE
   , L_RECEIPTDATE
   , L_SHIPINSTRUCT
   , L_SHIPMODE
   , L_COMMENT
from sb_ylw_ukd.tpch_lineitem limit 100000
) AS "tpch_lineitem"
LEFT JOIN (select 
     O_ORDERKEY
   , O_CUSTKEY
   , O_ORDERSTATUS
   , O_TOTALPRICE
   , O_ORDERDATE
   , O_ORDERPRIORITY
   , O_CLERK
   , O_SHIPPRIORITY
   , O_COMMENT
   from sb_ylw_ukd.tpch_orders
   limit 10000
) AS "tpch_orders" ON "tpch_lineitem".L_ORDERKEY = "tpch_orders".O_ORDERKEY
LEFT JOIN (select 
     PS_PARTKEY
   , PS_SUPPKEY
   , PS_AVAILQTY
   , PS_SUPPLYCOST
   , PS_COMMENT
   from sb_ylw_ukd.tpch_partsupp 
   limit 100000
   
) AS "tpch_partsupp" ON "tpch_lineitem".L_PARTKEY = "tpch_partsupp".PS_PARTKEY and "tpch_lineitem".L_SUPPKEY = "tpch_partsupp".PS_SUPPKEY

LEFT JOIN (select 
   S_SUPPKEY
 , S_NAME
 , S_ADDRESS
 , S_NATIONKEY
 , S_PHONE
 , S_ACCTBAL
 , S_COMMENT
from sb_ylw_ukd.tpch_supplier 
) AS "tpch_supplier" ON "tpch_partsupp".PS_SUPPKEY = "tpch_supplier".S_SUPPKEY
LEFT JOIN (select 
  C_CUSTKEY
, C_NAME , C_ADDRESS , C_NATIONKEY , C_PHONE , C_ACCTBAL , C_MKTSEGMENT , C_COMMENT  from sb_ylw_ukd.tpch_customer 
) AS "tpch_customer" ON "tpch_orders".O_CUSTKEY = "tpch_customer".C_CUSTKEY
LEFT JOIN (select 
   N_NATIONKEY
 , N_NAME
 , N_REGIONKEY
 , N_COMMENT
from sb_ylw_ukd.tpch_nation  order by N_NATIONKEY
) AS "tpch_nation" ON "tpch_supplier".S_NATIONKEY = "tpch_nation".N_NATIONKEY  WHERE ("tpch_supplier".S_SUPPKEY IS NOT NULL) AND ("tpch_customer".C_CUSTKEY IS NOT NULL) GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 LIMIT 1000000

My SQL query in Dbeaver

select * from view_tpch
where S_SUPPKEY is not null and C_CUSTKEY is not null

P.S.
I would also like to take this opportunity to note that I would also be happy to “vote” in favor of extending the naming to other languages .Referring to the issues

1. Playground member search doesn't support cyrillic letters  #3238
2. localization of the entire text #7665
   Although I understand that Localization is currently outside of Cube Core's scope, but still I would like to mention that as a member of your community I am very interested in this feature.

[oxoxlol]

I' sorry for being intrusive.

But maybe we should add another tag to this issue?

It's both “enhancement” and “help wanted” and maybe “bug”, I'm not sure.

But I really wanted to get feedback so I know that the request won't go to the bottom without a response.