join multiple tables to the same table using query class

Question

I'm using the query class to build a query that will have two or three tables join to the same table like this:

qbds1 = query.addDataSource(tablenum(Table1));

qbds2 = qbds1.addDataSource(tablenum(Table2));
qbds2.relations(true);

qbds3 = qbds2.addDataSource(tablenum(Table3));
qbds3.relations(true); 

qbds4 = qbds3.addDataSource(tablenum(Table4));
qbds4.relations(true);

qbds5 = qbds3.addDataSource(tablenum(Table5));
qbds5.relations(true);

qbds6 = qbds5.addDataSource(tablenum(Table6));
qbds6.relations(true);
qbds6.joinMode(JoinMode::ExistsJoin);

In this example I'm trying to join Table4 to Table3 and Table5 to Table3 but It doesn't work like I want; I've checked with the debugger and untill:

qbds5 = qbds3.addDataSource(tablenum(Table5))

everthing is ok; as soon as it executes this line the join between Table4 and Table3 disapears and the join between Table5 and Table3 is NOT added. The query sent to the SQL server is actually 2 queries, one between table1, table2 and table3 and another between table5 and table6 (the link to table4 doesn't exist). If I execute info (query.xml()) at the end, I see the joins between all tables like I wanted.

Is it even possible to achieve this using query class?

Answer 1

If you have more than one datasource on a join Level, you need to set the FetchMode of the datasources to 1:1 (default is 1:n).

In your case the query tree looks like

Table1
+ Table2
  + Table3
    + Table4
    + Table5
      + Table6

So Table4 and Table5 are on the same join level and need a FetchMode of 1:1. To set the FetchMode for a datasource, use a code line similar to

qbds2.fetchMode(QueryFetchMode::One2One);

See Magical FetchMode property and See how the fetch and join modes in Microsoft Dynamics AX queries work for additional Information.