SAP CDS中使用Predicate查询组合实体字段失败求助

实体定义 #

ProtestTypeConfigurations 实体 #

entity ProtestTypeConfigurations : cuid, managed {
    finder                       : DataType.finder;
    protestCategory                : ProtestCategory;
    code                             : DataType.Code;
    description                      : localized DataType.Description;  
    individualProtestType          : DataType.Flag default false;
    malCategory                     : MalCategory;
    protestTypeToMarAreaMappings : Composition of many protestTypeToMarAreaMappings
                                           on ProtestTypeToMarAreaMappings.protestTypeConfiguration = $self;
    isActive                         : Boolean default true;
}

protestTypeToMarAreaMappings 实体 #

entity protestTypeToMarAreaMappings : cuid, managed {
    salesOrganization          : SalesOrganization;
    distributionChannel        : DistributionChannel;
    division                   : Division;
    protestTypeConfiguration : ProtestTypeConfiguration;
}

错误的Predicate查询代码 #

尝试用Predicate查询组合实体字段，代码如下：

Predicate predicateSalesArea = CQL.get(ProtestTypeToMarAreaMappings.SALES_ORGANIZATION_ID).eq(salesOrganization)
        .and(CQL.get(ProtestTypeToMarAreaMappings.DISTRIBUTION_CHANNEL_ID).isNull()
                .and(CQL.get(ProtestTypeToMarAreaMappings.DIVISION_ID).isNull()))
        .or(CQL.get(ProtestTypeToMarAreaMappings.SALES_ORGANIZATION_ID).eq(salesOrganization)
                .and(CQL.get(ProtestTypeToMarAreaMappings.DISTRIBUTION_CHANNEL_ID).eq(distributionChannel)
                        .and(CQL.get(ProtestTypeToMarAreaMappings.DIVISION_ID).isNull())))
        .or(CQL.get(ProtestTypeToMarAreaMappings.SALES_ORGANIZATION_ID).eq(salesOrganization)
                .and(CQL.get(ProtestTypeToMarAreaMappings.DISTRIBUTION_CHANNEL_ID).eq(distributionChannel)
                        .and(CQL.get(ProtestTypeToMarAreaMappings.DIVISION_ID).eq(division))));

Predicate predicate = CQL.get(ProtestTypeConfigurations.IS_ACTIVE).eq(true)
          .and(CQL.get(ProtestTypeConfigurations.PROTEST_CATEGORY_CODE).eq(protestCategoryCode))
          .and(CQL.get(ProtestTypeConfigurations.PROTEST_TYPE_TO_SALES_AREA_MAPPINGS).isNull()).or(predicateSalesArea);

if(code!=null || description!=null) {
        predicate.and(CQL.get(ProtestTypeConfigurations.CODE).contains(code).and(CQL.get(ProtestTypeConfigurations.DESCRIPTION).contains(description)));
}

CqnSelect select=Select.from(ProtestTypeConfigurations_.class)
                .columns(a->a.ID(),b->b.code(),c->c.description(),
                        e->e.protestTypeToMarAreaMappings().salesOrganization_ID(),
                        f->f.protestTypeToMarAreaMappings().distributionChannel_ID(),
                        g->g.protestTypeToMarAreaMappings().division_ID(),
                        h->h.individualProtestType(),
                        i->i.malCategory_ID()).where(predicate);

filteredResult=db.run(select);

报错信息 #

No element with name 'salesOrganization_ID' in 'CustomerConfigurationService.ProtestTypeConfigurations' (service 'PersistenceService$Default', event 'READ', entity 'CustomerConfigurationService.ProtestTypeConfigurations')
    at com.sap.cds.services.impl.ServiceImpl.dispatch(ServiceImpl.java:256) ~[cds-services-impl-2.10.1.jar:na]
    at com.sap.cds.services.impl.ServiceImpl.emit(ServiceImpl.java:177) ~[cds-services-impl-2.10.1.jar:na]
    at com.sap.cds.services.ServiceDelegator.emit(ServiceDelegator.java:33) ~[cds-services-api-2.10.1.jar:na]
    at com.sap.cds.services.utils.services.AbstractCqnService.run(AbstractCqnService.java:53) ~[cds-services-utils-2.10.1.jar:na]
    at com.sap.cds.services.utils.services.AbstractCqnService.run(AbstractCqnService.java:43) ~[cds-services-utils-2.10.1.jar:na]
    at com.sap.ic.cmh.configuration.persistency.ProtestTypeConfigurationDao.getAllComplaintsWithWildCharacter(ProtestTypeConfigurationDao.java:179) ~[classes/:na]
    at jdk.internal.reflect.GeneratedMethodAccessor33.invoke(Unknown Source) ~[na:na]

可行但不适用于动态场景的CQN代码 #

使用CQN lambda写法可以实现查询，但业务需要动态拼接条件，这种写法会产生大量重复代码：

filteredResult = db
        .run(Select.from(ProtestTypeConfigurations_.class)
                .columns(a->a.ID(),b->b.code(),c->c.description(),
                        e->e.protestTypeToSalesAreaMappings().salesOrganization_ID(),
                        f->f.protestTypeToSalesAreaMappings().distributionChannel_ID(),
                        g->g.protestTypeToSalesAreaMappings().division_ID(),
                        h->h.individualComplaintType(),
                        i->i.itemCategory_ID())
                .where(d->d.isActive().eq(true).and(d.protestCategory_code().eq(protestCategoryCode).and(d.code().contains(code).or(d.description().contains(description))).and(not(d.protestTypeToSalesAreaMappings().exists())
                        .or(d.protestTypeToSalesAreaMappings().salesOrganization_ID().eq(salesOrganization)
                                .and(d.protestTypeToSalesAreaMappings().distributionChannel_ID().isNull()
                                        .and(d.protestTypeToSalesAreaMappings().division_ID().isNull())))
                        .or(d.protestTypeToSalesAreaMappings().salesOrganization_ID().eq(salesOrganization)
                                .and(d.protestTypeToSalesAreaMappings().distributionChannel_ID().eq(distributionChannel)
                                        .and(d.protestTypeToSalesAreaMappings().division_ID().isNull())))
                        .or(d.protestTypeToSalesAreaMappings().salesOrganization_ID().eq(salesOrganization)
                                .and(d.protestTypeToSalesAreaMappings().distributionChannel_ID().eq(distributionChannel)
                                        .and(d.protestTypeToSalesAreaMappings().division_ID().eq(division))))))));

错误原因 #

报错核心是：直接使用子实体protestTypeToMarAreaMappings的字段构建Predicate，但这些字段不属于主实体ProtestTypeConfigurations，必须通过组合关联路径来引用；同时原代码中逻辑运算符优先级错误，and与or的组合会导致条件逻辑混乱，且Predicate是不可变对象，拼接时需要重新赋值。

正确的Predicate构建方式 #

1. 构建组合实体的过滤Predicate #

针对组合实体的条件，需要通过CQL.exists(组合字段, 子实体条件)来关联子实体的查询条件，同时包含组合实体为空的情况：

// 构建销售区域匹配的Predicate，通过exists关联组合实体
Predicate predicateSalesArea = CQL.exists(ProtestTypeConfigurations.PROTEST_TYPE_TO_MAR_AREA_MAPPINGS, 
    CQL.get(ProtestTypeToMarAreaMappings.SALES_ORGANIZATION_ID).eq(salesOrganization)
    .and(
        CQL.get(ProtestTypeToMarAreaMappings.DISTRIBUTION_CHANNEL_ID).isNull()
        .and(CQL.get(ProtestTypeToMarAreaMappings.DIVISION_ID).isNull())
    ).or(
        CQL.get(ProtestTypeToMarAreaMappings.DISTRIBUTION_CHANNEL_ID).eq(distributionChannel)
        .and(CQL.get(ProtestTypeToMarAreaMappings.DIVISION_ID).isNull())
    ).or(
        CQL.get(ProtestTypeToMarAreaMappings.DISTRIBUTION_CHANNEL_ID).eq(distributionChannel)
        .and(CQL.get(ProtestTypeToMarAreaMappings.DIVISION_ID).eq(division))
    )
);

// 加上无组合实体的情况（即protestTypeToMarAreaMappings为空）
Predicate predicateSalesAreaWithEmpty = CQL.not(CQL.exists(ProtestTypeConfigurations.PROTEST_TYPE_TO_MAR_AREA_MAPPINGS))
    .or(predicateSalesArea);

2. 构建主实体基础条件并动态拼接 #

注意Predicate是不可变对象，每次拼接后需要重新赋值给变量；同时用括号明确逻辑分组，避免优先级错误：

// 初始化主实体基础条件
Predicate predicate = CQL.get(ProtestTypeConfigurations.IS_ACTIVE).eq(true)
    .and(CQL.get(ProtestTypeConfigurations.PROTEST_CATEGORY_CODE).eq(protestCategoryCode))
    .and(predicateSalesAreaWithEmpty); // 关联销售区域条件

// 动态拼接code/description过滤条件
if (code != null || description != null) {
    Predicate textPredicate = CQL.get(ProtestTypeConfigurations.CODE).contains(code);
    if (description != null) {
        textPredicate = textPredicate.or(CQL.get(ProtestTypeConfigurations.DESCRIPTION).contains(description));
    }
    predicate = predicate.and(textPredicate); // 重新赋值predicate
}

3. 完整的查询代码 #

// 1. 构建销售区域匹配条件（含空组合实体情况）
Predicate predicateSalesArea = CQL.exists(ProtestTypeConfigurations.PROTEST_TYPE_TO_MAR_AREA_MAPPINGS, 
    CQL.get(ProtestTypeToMarAreaMappings.SALES_ORGANIZATION_ID).eq(salesOrganization)
    .and(
        CQL.get(ProtestTypeToMarAreaMappings.DISTRIBUTION_CHANNEL_ID).isNull()
        .and(CQL.get(ProtestTypeToMarAreaMappings.DIVISION_ID).isNull())
    ).or(
        CQL.get(ProtestTypeToMarAreaMappings.DISTRIBUTION_CHANNEL_ID).eq(distributionChannel)
        .and(CQL.get(ProtestTypeToMarAreaMappings.DIVISION_ID).isNull())
    ).or(
        CQL.get(ProtestTypeToMarAreaMappings.DISTRIBUTION_CHANNEL_ID).eq(distributionChannel)
        .and(CQL.get(ProtestTypeToMarAreaMappings.DIVISION_ID).eq(division))
    )
);
Predicate predicateSalesAreaWithEmpty = CQL.not(CQL.exists(ProtestTypeConfigurations.PROTEST_TYPE_TO_MAR_AREA_MAPPINGS))
    .or(predicateSalesArea);

// 2. 初始化主条件
Predicate predicate = CQL.get(ProtestTypeConfigurations.IS_ACTIVE).eq(true)
    .and(CQL.get(ProtestTypeConfigurations.PROTEST_CATEGORY_CODE).eq(protestCategoryCode))
    .and(predicateSalesAreaWithEmpty);

// 3. 动态拼接文本过滤条件
if (code != null || description != null) {
    Predicate textPredicate = CQL.get(ProtestTypeConfigurations.CODE).contains(code);
    if (description != null) {
        textPredicate = textPredicate.or(CQL.get(ProtestTypeConfigurations.DESCRIPTION).contains(description));
    }
    predicate = predicate.and(textPredicate);
}

// 4. 构建并执行查询
CqnSelect select = Select.from(ProtestTypeConfigurations_.class)
        .columns(
            a -> a.ID(),
            b -> b.code(),
            c -> c.description(),
            e -> e.protestTypeToMarAreaMappings().salesOrganization_ID(),
            f -> f.protestTypeToMarAreaMappings().distributionChannel_ID(),
            g -> g.protestTypeToMarAreaMappings().division_ID(),
            h -> h.individualProtestType(),
            i -> i.malCategory_ID()
        )
        .where(predicate);

filteredResult = db.run(select);

关键注意点 #

• 组合实体的条件必须通过CQL.exists(组合字段, 子实体条件)来关联，不能直接引用子实体的字段。
• Predicate是不可变对象，每次调用and()/or()后会返回新的Predicate，必须重新赋值给变量，否则原条件不会更新。
• 逻辑运算符优先级：and优先级高于or，复杂条件必须用括号明确分组，避免逻辑错误。

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