ios - NSExpression尊重子查询NSPredicate

假设我有2个实体，Person和Transaction。
Person与Transaction有一对多关系，而Transaction实体具有amount和date。我的目标是基于NSFetchRequest创建一个Person，但是我只想知道在某些日期和交易金额的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/