Salesforce多态关联下Promotion查询及关联记录删除问题

• 业务需求：根据ProductId和/或BuyerGroupId查询Promotion记录，并删除其关联的PromotionTier、PromotionTarget、PromotionQualifier等子记录。
• 尝试的SOQL语句：

select id,
(select id from promotionTiers),
(select id from PromotionTargets where Target.Tecsys_item_num__c = '654321')
from Promotion

• 报错信息：

No such column 'Tecsys_item_num__c' on entity 'Name'. If you are attempting to use a custom field, be sure to append the '__c' after the custom field name. Please reference your WSDL or the describe call for the appropriate names.

• 报错原因：PromotionTargets中的TargetId是关联Product、Category的多态查找字段，直接通过Target.Tecsys_item_num__c过滤会触发错误——多态字段必须先指定具体关联的对象类型，才能访问对应对象的自定义字段。

基于Schema结构（Promotion与PromotionTargets、PromotionQualifiers、PromotionTiers为父子关系，TargetId为多态字段），针对不同查询条件给出正确SOQL：

1. 基于Product的自定义字段查询Promotion #

通过Target.Type指定关联对象为Product2，再结合Product的自定义字段Tecsys_item_num__c过滤：

SELECT Id,
       (SELECT Id FROM PromotionTiers),
       (SELECT Id FROM PromotionTargets WHERE Target.Type = 'Product2' AND Target.Product2.Tecsys_item_num__c = '654321'),
       (SELECT Id FROM PromotionQualifiers)
FROM Promotion
WHERE Id IN (
    SELECT PromotionId 
    FROM PromotionTarget 
    WHERE Target.Type = 'Product2' 
      AND Target.Product2.Tecsys_item_num__c = '654321'
)

2. 基于BuyerGroupId查询Promotion #

通过PromotionQualifier的关联字段BuyerGroupId过滤：

SELECT Id,
       (SELECT Id FROM PromotionTiers),
       (SELECT Id FROM PromotionTargets),
       (SELECT Id FROM PromotionQualifiers WHERE BuyerGroupId = 'YOUR_BUYER_GROUP_ID')
FROM Promotion
WHERE Id IN (
    SELECT PromotionId 
    FROM PromotionQualifier 
    WHERE BuyerGroupId = 'YOUR_BUYER_GROUP_ID'
)

3. 同时基于Product字段和BuyerGroupId查询Promotion #

组合两个子查询条件，筛选同时满足的Promotion：

SELECT Id,
       (SELECT Id FROM PromotionTiers),
       (SELECT Id FROM PromotionTargets WHERE Target.Type = 'Product2' AND Target.Product2.Tecsys_item_num__c = '654321'),
       (SELECT Id FROM PromotionQualifiers WHERE BuyerGroupId = 'YOUR_BUYER_GROUP_ID')
FROM Promotion
WHERE Id IN (
    SELECT PromotionId 
    FROM PromotionTarget 
    WHERE Target.Type = 'Product2' 
      AND Target.Product2.Tecsys_item_num__c = '654321'
)
AND Id IN (
    SELECT PromotionId 
    FROM PromotionQualifier 
    WHERE BuyerGroupId = 'YOUR_BUYER_GROUP_ID'
)

关键注意点 #

• 多态查找字段（如TargetId）必须先通过Type指定关联对象类型（如Product2），才能访问该对象的自定义字段。
• 主查询通过子查询过滤Promotion的ID，确保只返回符合条件的主记录，同时关联查询所有需要删除的子记录，方便后续批量删除操作。

内容的提问来源于stack exchange，提问作者Saad Amin