Core Data get sum of values. Fetched properties vs. propagation

Question

I'm relatively new to Core Data (coming from an SQLite background). Just finished reading the 'Core Data for iOS' book but I'm left with a number of baffling questions when I started modelling an app which has the following model:

1. 'Accounts' entity which has a to-many 'transactions' relationship and a 'startingBalance' property
2. 'Transaction' entity which has a to-many 'payments' relationship (and an inverse to Accounts)
3. 'Payment' entity which contains details of the actual 'amount' paid

For performance reasons I wanted to de-normalize the model and add a 'TotalAmountSpent' property in the 'Accounts' entity (as suggested by the book) so that I could simply keep updating that when something changed.

In practice this seems difficult to achieve with Core Data. I can't figure out how to do this properly (and don't know what the right way is). So my questions are:

a) Should I change the 'TotalAmountSpent' to a Fetched Property instead? Are there performance implications (I know it's loaded lazily but I will almost certainly be fetching that property for every account). If I do, I need to be able to get the total amount spent against the 'startingBalance' for a given period of time (such as the last three days). This seems easy in SQL but how do I do this in Core Data? I read I can use a @sum aggregate function but how do I filter on 'date' using @sum? I also read any change in the data will require refreshing the fetched property. How do I 'listen' for a change? Do I do it in 'Payment' entity's 'willSave' method?

b) Should I use propagation and manually update 'TotalAmountSpent' each time a new payment gets added to a transaction? What would be the best place to do this? Should I do it in an overridden NSManagedObject's 'willSave' method? I'm then afraid it'll be a nightmare to update all corresponding transactions/payments if the 'startingBalance' field was updated on the account. I would then have to load each payment and calculate the total amount spent and the final balance on the account. Scary if there are thousands of payments

Any guidance on the matter would be much appreciated. Thanks!

Orazio

Answer 1

If you use a fetched property you cannot then query on that property easily without loading the data into memory first. Therefore I recommend you keep the actual de-normalized data in the entity instead.

There are actually a few ways to easily keep this up to date.

1. In your -awakeFromFetch/-awakeFromInsert set up an observer of the relationship that will impact the value. Then when the KVO (Key Value Observer) fires you can do the calculation and update the field. Learning KVC and KVO is a valuable skill.

2. You can override -willSave in the NSManagedObject subclass and do the calculation on the save. While this is easier, I do not recommend it since it only fires on a save and there is no guarantee that your account object will be saved.

In either case you can do the calculation very quickly using the KVC Collection Operators. With the collection operators you can do the sum via a call to:

NSNumber *sum = [self valueForKeyPath:@"[email protected]"];

Rod

Thanks Marcus! I just finished reading your book on Core Data an hour ago by the way, great stuff! Just a question, so any time a transaction is modified or deleted, I then move upwards and update the account's remaining balance too? I know this is asking for too much but have you by any chance written an article on 'denormalization' that I could read? Much appreciated, truly! – Orazio

Also does that mean I'll have to make sure the relationships are prefetched (in order to setup KVO notifications)? I can't think of a situation where I wouldn't be fetching a Transaction object before updating it but thought I'd ask if there are caveats with this approach. Thanks again. – Orazio

If you are changing the value inside of a Transaction then yes the transaction will need to "ping" its Account to update the value. If you are only adding or removing Transaction entities (which makes sense to me for starting balances) then the Account can just monitor the transactions relationship and update itself. Depends on how your app is designed. – Rod

No, you do not need to pre-fetch the relationships. The KVO will work just fine without pre-fetching. – Rod

I tried adding an observer to the 'parent' account from within 'Transaction's awakeFromInsert and awakeFromFetch but just realized it doesn't work because the 'account' hasn't even been assigned yet to the Transaction by the time awakeFromInsert is invoked. I'm really sorry but I can't figure out where to add the observers to inform the parent of the change when the parent hasn't faulted yet or hasn't been assigned yet. And if I override 'setAccount' in 'Transaction' and add / remove the observer there, it still doesn't work as the 'amount' has already been set and account isn't informed. – Orazio

you add the observer to the Account entity's -awakeFromInsert and -awakeFromFetch so that it can be notified when a Transaction is added to the transactions relationship. This is assuming that you are setting the value before you add the Transaction entity to the relationship. – Rod

Be careful when calculating the sum using "@sum.startingBalance". Last time I checked, it internally converted every number to string and parsed back to number for calculating the sum. If you run into performance problems take a look at it using Instruments. Regarding KVO, I think you already found my code fragment on github solving this problem (github.com/mbrugger/CoreDataDependentProperties) – Jotunheim

@MartinBrugger, do you have any data or test cases to support that claim? I have used the collection accessor methods many times and have not run across any situations where the accessor is converted from a number to a string and then back to a number. That sounds like something that would have been fixed if true. – Rod

@Marcus I first noticed this behavior in 10.5.8 long ago and did not check it in recent versions. I will take a look at it again. – Jotunheim

@MarcusS.Zarra the problem still exists i simply calculate invoicesSum = [[self valueForKeyPath:@"[email protected]"] doubleValue]; The stacktrace in CPU Sampler shows some NSString being parsed into a NSNumber. If you are interested in sample code and stacktrace as screenshot I can send you an email or discuss it somewhere else. – Jotunheim

@MartinBrugger please send me the sample, I am quite interested. marcus at cimgf dot com. – Rod