gpt4 book ai didi

ios - NSExpression 尊重子查询 NSPredicate

转载 作者:可可西里 更新时间:2023-11-01 04:39:45 26 4
gpt4 key购买 nike

假设我有 2 个实体,一个 Person 和一个 Transaction

PersonTransactionTransaction 实体有amountdate 的一对多关系。我的目标是有一个基于 PersonNSFetchRequest,但我只想知道在特定日期和 SUM 之间有交易的人交易金额。

我的代码如下:

NSExpression *amountKeyPath = [NSExpression expressionForKeyPath:@"transactions.amount"];
NSExpression *sumAmountExpression = [NSExpression expressionForFunction:@"sum:" arguments:@[amountKeyPath]];

// Create the expression description for that expression.
NSExpressionDescription *description = [[NSExpressionDescription alloc] init];
[description setName:@"sum"];
[description setExpression:sumAmountExpression];
[description setExpressionResultType:NSDecimalAttributeType];

// Create the sum amount fetch request,
self.sumAmountFetchRequest = [NSFetchRequest fetchRequestWithEntityName:@"Person"];
self.sumAmountFetchRequest.resultType = NSDictionaryResultType;
self.sumAmountFetchRequest.predicate = [NSPredicate predicateWithFormat:@"SUBQUERY(transactions, $t, $t.date >= %@ AND $t.date <= %@).@count > 0", self.startDate, self.endDate];
self.sumAmountFetchRequest.propertiesToFetch = @[@"name", description];

一切似乎都很好但是......当我查看生成的 SQL 查询时,它看起来像:

SELECT t0.ZPERSONID, t0.ZNAME, 
(SELECT TOTAL(t2.ZAMOUNT) FROM ZTRANSACTION t2 WHERE (t0.Z_PK = t2.ZPERSON) )
FROM ZPERSON t0
WHERE (SELECT COUNT(t1.Z_PK) FROM ZTRANSACTION t1 WHERE (t0.Z_PK = t1.ZPERSON AND (( t1.ZDATE >= ? AND t1.ZDATE <= ?))) ) > ?

因此,声明的 NSExpression 似乎不遵守创建的 fetchRequest 的 NSPredicate

是否有可能使表达式也遵守附加到 fetchRequest 的谓词?或者因为 NSExpression 独立存在,我应该给它附加另一个谓词?

最佳答案

我没有任何证据支持我自己的回答,但在为此苦苦挣扎了几个小时之后。我认为这不是 NSExpression 不尊重 fetchRequest 的 NSPredicate 而是因为 fetchRequest 的谓词是一个子查询。

SELECT t0.ZPERSONID, t0.ZNAME, 
(SELECT TOTAL(t2.ZAMOUNT) FROM ZTRANSACTION t2 WHERE (t0.Z_PK = t2.ZPERSON) )
FROM ZPERSON t0
WHERE
(SELECT COUNT(t1.Z_PK) FROM ZTRANSACTION t1 WHERE (t0.Z_PK = t1.ZPERSON AND (( t1.ZDATE >= ? AND t1.ZDATE <= ?))) ) > ?

所以,谓词中的子查询并没有真正转化为我想要的WHERE子句。我认为如果它改为创建一个 JOIN,那会起作用。

我最终的解决方案是反过来。如果我改为从 Transaction 创建 fetchRequest,那效果很好。

NSExpression *amountKeyPath = [NSExpression expressionForKeyPath:@"amount"];
NSExpression *sumAmountExpression = [NSExpression expressionForFunction:@"sum:" arguments:@[amountKeyPath]];

// Create the expression description for that expression.
NSExpressionDescription *description = [[NSExpressionDescription alloc] init];
[description setName:@"sum"];
[description setExpression:sumAmountExpression];
[description setExpressionResultType:NSDecimalAttributeType];

// Create the sum amount fetch request,
self.sumAmountFetchRequest = [NSFetchRequest fetchRequestWithEntityName:@"Transaction"];
self.sumAmountFetchRequest.resultType = NSDictionaryResultType;
self.sumAmountFetchRequest.predicate = [NSPredicate predicateWithFormat:@"date >= %@ AND date <= %@", self.startDate, self.endDate];
self.sumAmountFetchRequest.propertiesToFetch = @[@"person.name", description];
self.sumAmountFetchRequest.propertiesToGroupBy = @[@"person.name"];

这很完美。它必须按 "person.name" 分组,以便根据需要使用 sum:

生成的 SQL 是

SELECT  t1.ZPERSONID, total( t0.ZAMOUNT) 
FROM ZTRANSACTION t0
LEFT OUTER JOIN ZPERSON t1 ON t0.ZPERSON = t1.Z_PK
WHERE ( t0.ZDATE >= ? AND t0.ZDATE <= ?) GROUP BY t1.ZPERSONID

干杯,

关于ios - NSExpression 尊重子查询 NSPredicate,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/17591616/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com