GOSU query.make Join from primary table twice

I am trying to write the below SQL query utilizing Gosu's Query.make. I tried to join LineItem#TAccount after joining in the transaction table, but it doesn't work.

Here is the error, "column must be a column in the current table object or it must be a foreign key to the current table object. It is from table entity.LineItem to table entity.Transaction"

The problem seems to be when I bring in the transaction table. I think it is trying to join the transaction table with the TAccount table and the join is from the LineItem table to the Transaction table.

var transactionsFromTransactionQuery = Query.make(LineItem)
        .join(LineItem#TAccount)
        .join(LineItem#Transaction)
        .or(\orCriteria -> {
          orCriteria.compare("Subtype", Relop.Equals, Transaction.TC_CHARGEPAIDFROMUNAPPLIED)
          orCriteria.compare("Subtype", Relop.Equals, Transaction.TC_CHARGEWRITTENOFF)
          orCriteria.compare("Subtype", Relop.Equals, Transaction.TC_INITIALCHARGETXN)
        })
        .join("TAccountContainer", PolicyPeriod, "HiddenTAccountContainer")
        .compare(PolicyPeriod#PolicyNumberLong, Relop.Equals, "My policy number")

SELECT txn.* 
FROM bc_transaction txn
inner join bc_lineitem li on li.TransactionID = txn.ID
inner join bc_taccount ta on ta.id = li.TAccountID
inner join bc_policyperiod pp on pp.HiddenTAccountContainerID = ta.TAccountContainerID
inner join bctl_transaction tSub
    on txn.Subtype = tSub.ID
where pp.PolicyNumberLong = 'myPolicyNumber'
and tSub.TYPECODE in ('ChargePaidFromUnapplied','ChargeWrittenOff','InitialChargeTxn')```

Solution

With the updates and additional information about your issue I think I see what you're having an issue with. Specifically, the way you're chaining operations isn't respecting the context you think it is. Each join returns a table object for the joined entity, not the root entity of the query.make(). So your later operations are trying to reference columns from a different entity than is in scope.

Please review this version of the query and see if it works for you. Note that I don't have a BC studio up to validate my rewrite, so I might have the join references on the wrong table, but hopefully I've got it close.

var transactionsFromTransactionQuery = Query.make(LineItem)
var tacctTable = transactionsFromTransactionQuery.join(LineItem#TAccount)
var tranTable = transactionsFromTransactionQuery.join(LineItem#Transaction)
    .or(\orCriteria -> {
      orCriteria.compare("Subtype", Relop.Equals, Transaction.TC_CHARGEPAIDFROMUNAPPLIED)
      orCriteria.compare("Subtype", Relop.Equals, Transaction.TC_CHARGEWRITTENOFF)
      orCriteria.compare("Subtype", Relop.Equals, Transaction.TC_INITIALCHARGETXN)
    })
var polPerTable = tacctTable.join("TAccountContainer", PolicyPeriod, "HiddenTAccountContainer")
    .compare(PolicyPeriod#PolicyNumberLong, Relop.Equals, "My policy number")