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?

It should be possible, try `.relations(false)` and manually create the relations with `addLink(...)` — Alex Kwitny, Apr 20 '15 at 16:59

FH-Inway · Accepted Answer · 2015-04-20T19:58:36.940

3

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.

edited Apr 20 '15 at 19:58

answered Apr 20 '15 at 17:26

FH-Inway

4,432
1
20
37

See also http://stackoverflow.com/questions/12322979/query-object-not-working-as-expected for a similar question. – FH-Inway Apr 20 '15 at 17:28
I was just looking at the last link before seeing your answer and I realized that I have to add fetchMode(QueryFetchMode::One2One) to table4 and table5 join (I did it before posting but I guess I didn't do it for table4 AND table5). Thank you so much! – Alex Apr 20 '15 at 18:59

score 1 · Answer 2 · answered Apr 20 '15 at 19:04

1

FH-Inway answer is correct: just add

qbdsX.fetchMode(QueryFetchMode::One2One);

for every join on the same level.

answered Apr 20 '15 at 19:04

Alex

78
1
1
7

join multiple tables to the same table using query class

2 Answers2