NSExpression respect a subquery NSPredicate

Question

Assume that I have 2 entities, a Person and a Transaction.

Person have a to-many relationship with Transaction and Transaction entity has amount and date. My goal was to have a NSFetchRequest based on the Person, but I only want to know persons who have transactions between certain dates and the SUM of the transactions amount.

My code looks like:

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];

Everything seems fine but... When I looked at the SQL Query generated, it looks like:

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 <= ?))) ) > ?

So, it seems that the NSExpression declared, doesnt respect the NSPredicate of the fetchRequest created.

Would that be possible to make the expression also respect the predicate attached to the fetchRequest? Or since NSExpression stands by itself, I should attach another predicate to it?

The SQLite statement looks good to me. The predicate is transformed to the last "WHERE" and that applies to the whole statement. Do you get the expected result? — Martin R, Jul 11 '13 at 11:34
No, I got the amount of transactions belong to that person (not limited to the time frame -- WHERE clause) — Rpranata, Jul 11 '13 at 11:52
OK, now I understand what you mean, and my above comment is wrong. — Martin R, Jul 11 '13 at 12:00

Rpranata · Accepted Answer · 2013-07-11T14:32:01.200

I dont have anything to back my own answer, but after struggling with this for hours. I thought that its not that the NSExpression not respecting the fetchRequest's NSPredicate but its more to the fact that the predicate of the fetchRequest is a subquery.

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 <= ?))) ) > ?

So, the subquery in the predicate is not really transformed into the WHERE clause I want. I think if it was creating a JOIN instead, that'd work.

My solution in the end is to work the other way around. If I create the fetchRequest from Transaction instead, that work perfectly.

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

That works perfectly. It has to be grouped by the "person.name" so that it would use the sum: as wanted.

The SQL generated would be

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

Cheers,

NSExpression respect a subquery NSPredicate

1 Answers1