使用Gosu Query.make实现表关联时遇到外键关联错误求助

问题说明 #

使用Gosu的Query.make编写查询实现指定SQL逻辑时，关联Transaction表后再关联LineItem#TAccount失败，报错信息：

"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"

问题核心：关联Transaction后，查询上下文自动切换到了Transaction表，后续程序误以为要将Transaction与TAccount关联，但实际关联关系是LineItem到TAccount，导致匹配错误。

当前Gosu代码 #

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")

目标SQL语句 #

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')

解决方案 #

核心是明确指定每个关联的起始表，避免上下文自动切换导致的关联错误，以下两种方式均可解决：

方式一：调整关联顺序+明确关联路径 #

先关联Transaction，后续关联TAccount和PolicyPeriod时，明确从LineItem出发指定关联链：

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

方式二：使用别名固定关联表 #

给每个关联表设置别名，后续操作通过别名明确指定操作的表：

var transactionsQuery = Query.make(LineItem).alias("li")
        .join("li.TAccount").alias("ta")
        .join("li.Transaction").alias("txn")
        .or(\orCriteria -> {
          orCriteria.compare("txn.Subtype", Relop.Equals, Transaction.TC_CHARGEPAIDFROMUNAPPLIED)
          orCriteria.compare("txn.Subtype", Relop.Equals, Transaction.TC_CHARGEWRITTENOFF)
          orCriteria.compare("txn.Subtype", Relop.Equals, Transaction.TC_INITIALCHARGETXN)
        })
        .join("ta.TAccountContainer", PolicyPeriod, "HiddenTAccountContainer").alias("pp")
        .compare("pp.PolicyNumberLong", Relop.Equals, "My policy number")
        .select("txn")

关键提示 #

Gosu Query API在执行join后会自动将当前上下文切换到关联的表，若后续关联未明确指定起始表，会默认从当前上下文表查找关联关系。通过调整顺序或使用别名，能清晰告知API每个关联的来源，避免上下文混淆。

内容的提问来源于stack exchange，提问作者Brandon Hunt