How to use QueryBuilder for multiple child joins?

(0) Share

Report

Posted on by Community Member

I'd like to recreate this SQL code here, using QueryBuilder:

select Field1 from TableA
inner join TableB
    on TableA.F1 = TableB.F1
inner join TableC
    on TableC.F2 = TableA.F2 --note TableA again

This is the code I've generated so far:

QueryBuildDataSource dataSource;
    QueryBuildDataSource dataSourceOMInternalOrg;
    QueryBuildDataSource dataSource2;
    str textDesc = "";

    query = new Query();

    dataSource = query.addDataSource(tableNum(LogisticsPostalAddress), "LogisticsPostalAddress");

    dataSource.relations(false);

    dataSource = dataSource.addDataSource(tableNum(LogisticsAddressCountryRegion), "LogisticsAddressCountryRegion");
    dataSource.addLink(fieldNum(LogisticsAddressCountryRegion, CountryRegionId), fieldNum(LogisticsPostalAddress, CountryRegionId));

    dataSource.joinMode(JoinMode::OuterJoin);

    dataSource = dataSource.addDataSource(tableNum(DirPartyLocation), "DirPartyLocation");
    dataSource.addLink(fieldNum(LogisticsPostalAddress, Location), fieldNum(DirPartyLocation, Location));

    dataSource.joinMode(JoinMode::InnerJoin);

    dataSource = dataSource.addDataSource(tableNum(DirPartyTable), "DirPartyTable");
    dataSource.addLink(fieldNum(DirPartyLocation, Party), fieldNum(DirPartyTable, RecId));
    dataSource.addLink(fieldNum(DirPartyLocation, Location), fieldNum(DirPartyTable, PrimaryAddressLocation));

    dataSource = dataSource.addDataSource(tableNum(OMInternalOrganization), "OMInternalOrganization");
    dataSource.addLink(fieldNum(OMInternalOrganization, RecId), fieldNum(DirPartyTable, RecId));

    dataSource.joinMode(JoinMode::OuterJoin);

But it doesn't work since I need my projection to start at LogisticsPostalAddress and L..P...A...CountryRegion and DirPartyLocation both need to be linked with LogisticsPostalAddress. At the second time I try to link LogisticsPostalAddress I get an error because the last datasource added was not that one.

I don't know how to do that using QueryBuilder, is it possible? If so, how?

This is the full structure of the query I'm trying to assemble:

Thanks!

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (1)

Suggested answer

Anton Venter 20,345 Super User 2025 Season 2 on at

Like (0)

Report

Have a look at this article on Axaptapedia.com

Was this reply helpful? Yes No
Suggested answer

Ivan (Vanya) Kashperuk on at

Like (1)

Report

You forgot to use FetchMode One2One for these datasorces

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks, but I just read the article and couldn't find the solution to my problem. Could you please point me to that? Thanks.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Ok, I'll try that now.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hey Ivan, even using dataSource.fetchMode(QueryFetchMode::One2One); I still got the error of field does not exist "Invalid Field/Related field combination".

Was this reply helpful? Yes No
Verified answer

Ivan (Vanya) Kashperuk on at

Like (1)

Report
static void Job3(Args _args) { Query query; QueryBuildDataSource dataSourceLPA; QueryBuildDataSource dataSourceLACR; QueryBuildDataSource dataSourceOMInternalOrg; QueryBuildDataSource dataSourceDPL; QueryBuildDataSource dataSourceDPT; str textDesc = ""; query = new Query(); dataSourceLPA = query.addDataSource(tableNum(LogisticsPostalAddress)); dataSourceLACR = dataSourceLPA.addDataSource(tableNum(LogisticsAddressCountryRegion)); dataSourceLACR.addLink(fieldNum(LogisticsPostalAddress, CountryRegionId), fieldNum(LogisticsAddressCountryRegion, CountryRegionId)); dataSourceLACR.fetchMode(QueryFetchMode::One2One); dataSourceLACR.joinMode(JoinMode::OuterJoin); dataSourceDPL = dataSourceLPA.addDataSource(tableNum(DirPartyLocation), "DirPartyLocation"); dataSourceDPL.addLink(fieldNum(LogisticsPostalAddress, Location), fieldNum(DirPartyLocation, Location)); dataSourceDPL.fetchMode(QueryFetchMode::One2One); dataSourceDPL.joinMode(JoinMode::InnerJoin); dataSourceDPT = dataSourceDPL.addDataSource(tableNum(DirPartyTable), "DirPartyTable"); dataSourceDPT.addLink(fieldNum(DirPartyLocation, Party), fieldNum(DirPartyTable, RecId)); dataSourceDPT.addLink(fieldNum(DirPartyLocation, Location), fieldNum(DirPartyTable, PrimaryAddressLocation)); dataSourceOMInternalOrg = dataSourceDPT.addDataSource(tableNum(OMInternalOrganization), "OMInternalOrganization"); dataSourceOMInternalOrg.addLink(fieldNum(OMInternalOrganization, RecId), fieldNum(DirPartyTable, RecId)); dataSourceOMInternalOrg.joinMode(JoinMode::OuterJoin); info(query.dataSourceNo(1).toString()); }

When using addLink, you need to specify the parent field first. In your code you had it backwards, thus the error.

The result from the above is:

SELECT * FROM LogisticsPostalAddress(LogisticsPostalAddress_1) OUTER JOIN * FROM LogisticsAddressCountryRegion(LogisticsAddressCountryRegion_1) ON LogisticsPostalAddress.CountryRegionId = LogisticsAddressCountryRegion.CountryRegionId JOIN * FROM DirPartyLocation(DirPartyLocation) ON LogisticsPostalAddress.Location = DirPartyLocation.Location JOIN * FROM DirPartyTable(DirPartyTable) ON DirPartyLocation.Party = DirPartyTable.RecId AND DirPartyLocation.Location = DirPartyTable.PrimaryAddressLocation OUTER JOIN * FROM OMInternalOrganization(OMInternalOrganization) ON DirPartyTable.RecId = OMInternalOrganization.RecId

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks Ivan, yet again you save me xD

I didn't know about this multiple dataset. Thanks for the help!

Was this reply helpful? Yes No