Chained expressions to perform calculations in Core Data

Question

First of all: Happy new year :-)

What I am trying to do

I am trying to divide two attributes in Core Data and then calculate the average of these divisions. The attributes are specified by a key path (e.g. eur, usd, aud).

Example: I have the following data set:

date         eur   usd    aud
------------------------------
2010-01-01   0.5   1.0    1.5
2010-01-02   0.6   1.1    1.6
2010-01-03   0.4   1.0    1.3

Divide two attributes, e.g. eur / usd with the follwowing results...

divide eur / usd:
------------------
2010-01-01   0.5
2010-01-02   0.54
2010-01-03   0.4

... then calculate the average of these numbers (0.5 + 0.54 + 0.4)/3 = 0.48

My code

Since I would like to have these calculations performed directly by Core Data, I created the following expressions and fetch request:

NSExpression *fromCurrencyPathExpression = [NSExpression
    expressionForKeyPath:fromCurrency.lowercaseString];
NSExpression *toCurrencyPathExpression   = [NSExpression
    expressionForKeyPath:toCurrency.lowercaseString]; 
NSExpression *divisionExpression = [NSExpression
   expressionForFunction:@"divide:by:"
   arguments:@[fromCurrencyPathExpression,
   toCurrencyPathExpression]];
    
NSExpression *averageExpression = [NSExpression expressionForFunction:@"average:"
   arguments:@[divisionExpression]];

NSString *expressionName = @"averageRate";
NSExpressionDescription *expressionDescription = 
   [[NSExpressionDescription alloc] init];
expressionDescription.name = expressionName;
expressionDescription.expression = averageExpression;
expressionDescription.expressionResultType= NSDoubleAttributeType;

NSFetchRequest *request = [NSFetchRequest 
   fetchRequestWithEntityName:NSStringFromClass([self class])];
NSPredicate *predicate =
   [NSPredicate predicateWithFormat:@"date >= %@ AND date <= %@",startDate,fiscalPeriod.endDate];
    
request.predicate = predicate;
request.propertiesToFetch = @[expressionDescription];
request.resultType = NSDictionaryResultType;
     
NSError *error;
NSArray *results = [context 
    executeFetchRequest:request error:&error];

The problem

However, when running the app, it crashes with the error message:

Unsupported argument to sum : (
    "eur / usd"

What is wrong with my code? How can I chain the two calculations and have them performed directly in Core Data?

Thank you!

Here are similar problems: http://stackoverflow.com/questions/13879025/fetch-aggregate-data-from-nsmanagedobject-using-another-expression-as-argument-t, http://stackoverflow.com/questions/8985194/performing-multiplication-aggregation-with-coredata-how-to. So it seems just to be unsupported. — Martin R, Jan 02 '13 at 12:46
Would it be possible to split the fetch request in two separate requests, one for the division and one for the calculation of the average number? — AlexR, Jan 02 '13 at 14:09
Probably. You could execute a fetch request that returns an array with the results of all divisions, and then calculate the average in memory, using key-value-coding (something like `[results valueForKey:@"@avg:..."]`. I cannot test this at this moment, perhaps it helps already, otherwise I will try to give explicit code later... — Martin R, Jan 02 '13 at 14:17
Is there a performance hit when calculating the averages in memory instead doing this directly in the database? In my current implementation, I do everything in memory (fetching the data sets, division and aggregation). Since this very slow, I am hoping for a significant improvement in performance when doing this in the data base. Btw: I would really appreciate if you could show me some code on how to do the division in the data base. Thank you! — AlexR, Jan 02 '13 at 14:21
I have finally added an answer with some code. You probably figured out a solution yourself in the meantime, but perhaps it is useful. I cannot really comment on the performance question, this is something you have to test and compare. — Martin R, Jan 10 '13 at 20:48

score 5 · Accepted Answer · edited May 23 '17 at 12:08

It seems that "collection" function expressions like @average can only be used with key paths, but not in combination with other general expressions, when used as propertiesToFetch. I do not have a reference for that, but it is a problem that others have noticed also:

So you could proceed in two steps: First execute a fetch request that returns an array with the results of all divisions:

NSExpression *fromCurrencyPathExpression = [NSExpression
                                            expressionForKeyPath:@"eur"];
NSExpression *toCurrencyPathExpression   = [NSExpression
                                            expressionForKeyPath:@"usd"];
NSExpression *divisionExpression = [NSExpression
                                    expressionForFunction:@"divide:by:"
                                    arguments:@[fromCurrencyPathExpression,
                                    toCurrencyPathExpression]];

NSString *expressionName = @"ratio";
NSExpressionDescription *expressionDescription =
[[NSExpressionDescription alloc] init];
expressionDescription.name = expressionName;
expressionDescription.expression = divisionExpression;
expressionDescription.expressionResultType= NSDoubleAttributeType;

NSFetchRequest *request = [NSFetchRequest fetchRequestWithEntityName:@"Entity"];

request.propertiesToFetch = @[expressionDescription];
request.resultType = NSDictionaryResultType;

NSError *error;
NSArray *ratios = [context executeFetchRequest:request error:&error];

The result is an array of dictionaries:

(lldb) po ratios
(NSArray *) $0 = 0x00000001001170f0 <_PFArray 0x1001170f0>(
{
    ratio = "0.5454545454545454";
},
{
    ratio = "0.4";
},
{
    ratio = "0.5";
}
)

Also, with the "-com.apple.CoreData.SQLDebug 1" option one can see that the divisions are already executed on the SQLite level:

sql: SELECT  t0.ZEUR /  t0.ZUSD FROM ZENTITY t0

Then you can compute the average of all ratios in memory, using Key-Value Coding:

NSNumber *average = [ratios valueForKeyPath:@"@avg.ratio"];

and the result is

(lldb) po average
(NSNumber *) $0 = 0x000000010012fd60 0.4818181818181818

Thank you for your great answer, Martin! This is exactly how I did it my self and it works great. I assume that the `valueForKeyPath`command is executed in memory and not in SQL. — AlexR, Jan 11 '13 at 07:29
@AlexR: Yes, `ratios` is a plain dictionary which does not have any connections to the database. `valueForKeyPath` operates only on that dictionary. — Martin R, Jan 11 '13 at 07:46

Chained expressions to perform calculations in Core Data

What I am trying to do

My code

The problem

1 Answers1

Linked