CODEWITHSUNDEEP
acumatica
Yuriy Zaletskyy
Yuriy Zaletskyy

Reputation: 5151

How Acumatica treats five inner joins of classes which are relatives

In my Graph I have following Inner joins chain:

    public PXSelectJoin<APRegister,
        InnerJoin<APPayment, On<APPayment.docType, Equal<APRegister.docType>>,
            InnerJoin<CATran, On<CATran.origTranType, Equal<APRegister.docType>>,
                InnerJoin<Vendor, On<Vendor.bAccountID, Equal<APRegister.vendorID>>,
                    InnerJoin<BAccount, On<BAccount.bAccountID, Equal<BAccount.bAccountID>>,
                        InnerJoin<VendorClass, On<VendorClass.vendorClassID, Equal<Vendor.vendorClassID>>>>>>>> ReleasedPayments;

and I'm particularly interested how Acumatica framework "feels" and maybe behaves about inner joins between classes that are relatives. For example APRegister is basic class to APPayment, and BAccount is basic class to Vendor. Will Acumatica framework be able to handle them properly? I give this question because mentioned query caused my processor to be over loaded, memory exhausted and then crashing w3wp process. So I've get an impression that something is missing

Upvotes: 0

Views: 737

Answers (2)

Tony Lanzer
Tony Lanzer

Reputation: 291

I noticed you were joining BAccount.bAccountID to itself. You likely meant to join to Vendor. Since BAccount is only joined to itself and not the other tables in any way, it might be acting like a Cartesian join as it would if there were no join condition specified. If you truly would like to join BAccount.bAccountID to itself for some reason, you'll likely want to use BAccountR or BAccount2 -- which are subclasses of BAccount -- or subclass it yourself. This should then use a different alias within the generated SQL statement and produce expected results.

Note: You might possibly not even need a BAccount join to Vendor. I discovered that a select from BAccount already includes an inner join to Vendor by default. The subclass BAccountR does not, though.

Upvotes: 1

Brendan
Brendan

Reputation: 5613

My thought is it should work as long as the joins are correct. I remember having issues when trying to do a join on the same table using the same table multiple times. As long as the class name for BQL was different it worked just fine. Without testing it you really will not know for sure.

In reference to two of the same tables in a join: Acumatica BQL Query with the same table more than once

Upvotes: 0

Related Questions