CODEWITHSUNDEEP
sqlacumatica
Anth12
Anth12

Reputation: 1897

Acumatica BQL with multiple joins

I have a custom table that stores a reference to the Users.PKID Guid & need to execute the following simple SQL query through the Acumatica framework (BQL).

SELECT 
    -- *
    L.LeaveTransactionId,
    L.EmployeeId,
    E.Name AS EmployeeName,
    E.ManagerId,
    M.Name AS ManagerName,
    M.UserId AS ManagerUserId
FROM LeaveTransaction AS L

INNER JOIN Employee AS E
    ON L.EmployeeId = E.EmployeeId

INNER JOIN Employee AS M
    ON E.ManagerId = M.EmployeeId

WHERE M.UserId = '...'

I have a single join as follows but cannot see any method to specify multiple joins.

public PXProcessingJoin<LeaveTransaction,
            InnerJoin<DAC.Employee, On<DAC.Employee.employeeId, Equal<DAC.LeaveTransaction.employeeId>>>> LeaveTransactions;

Is there any way to expand the BQL to join 2 or more tables?

Please note that the data should be merged and not read-only as the dataset is also filtered on a Selected bool allowing users to select 1 or more items in the grid view.

Upvotes: 0

Views: 2483

Answers (1)

Anth12
Anth12

Reputation: 1897

There is an overload for all implementations of the BaseJoin type with a 'NextJoin' generic parameter e.g. InnerJoin<Table, On, NextJoin>.

The sql example above is represented with:

public PXProcessingJoin<LeaveTransaction,
            InnerJoin<DAC.Employee, On<DAC.Employee.employeeId, Equal<DAC.LeaveTransaction.employeeId>>,
            InnerJoin<DAC.Manager, On<Manager.employeeId, Equal<DAC.Employee.managerId>>>>,
            Where<LeaveTransaction.status, Equal<LeaveStatus.submitted>,
                And<Manager.userId, Equal<Current<AccessInfo.userID>>>>> LeaveTransactions;

Because this example contains a circular Foreign Key, an additional Data Access Class (DAC) derived from the standard definition is needed inorder to generate the correct SQL query. In this scenario the Manager class is defined as a derived type from Employee, any fields queried in BQL specific to this class must be overridden (otherwise the generated SQL will reference the base type).

[Serializable]
public class Manager : Employee
{
    public new abstract class employeeId : IBqlField {}
    public new abstract class userId : PX.Data.IBqlField {}
}

Upvotes: 1

Related Questions