2

How do I translate SQL:

select sum( field1 * field2 ) from table where field3 = 8

to equivalent CoreData NSPredicate code, please? Is it possible without fetching everything and running an ugly for loop?

Piotr Farbiszewski
  • 71
  • 6

2 Answers2

2

Why does it need to be performed in the SQL?

If you are using an NSManagedObject sub class then you can just create a virtual property that will do that calculation. (Ignore the naming I do not know you domain)

MyClass.h

@property (nonatomic, readonly) CGFloat sumOfField1AndField2;

MyClass.m

- (CGFloat)sumOfField1AndField2;
{
   return self.field1 * self.field2;
}
Paul.s
  • 38,494
  • 5
  • 70
  • 88
  • I guess if it's done through SQL, performance would be superior. If it cannot be done, I'll follow your suggestion, thanks. – Piotr Farbiszewski Nov 13 '11 at 21:22
  • If you really notice a big problem when profiling your app then it might then be time to reassess this method, but until then the simplest things is often the best and premature optimisation is almost always bad. – Paul.s Nov 13 '11 at 21:26
  • This is quite valid question (may be example is not quite understandable) consider this : entity Drink with properties dateOfDrink, countOfDrinksTaken and pricePerDrink and I want to query database for costs within date range - I would like to fetch sum(countOfDrinksTaken * pricePerDrink). – jki Dec 14 '12 at 12:26
  • This need to be performed in SQL because fetching all the managed objects eats up a lot of memory. – Piotr Farbiszewski Jul 17 '13 at 04:41
1

Your entity should have a fetched property calculated property that multiplies the two fields. The solution to your query is to combine NSPredicate and key-value-coding:

fetchRequest = [NSFetchRequest fetchRequestWithEntityName:@"table"];
fetchRequest.predicate = [NSPredicate predicateWithFormat:@"field3 = %@", @8];
NSArray *results = [self.managedObjectContext 
       executeFetchRequest:fetchRequest error:nil]; 

NSNumber *sum = [results valueForKeyPath:@"@sum.fetchedProperty"];

As you pointed out, it is really not a fetched property but a calculated field. I made a test along these lines: 

// NSManagedObjectSubclass .h
@property (nonatomic, strong) NSNumber *product;

// .m
@dynamic product;

-(NSNumber *)product { 
   return @(value1.floatValue * value2.floatValue); 
}

and this worked: 

NSLog(@"Sum of products: %@", [fetchedObjects valueForKeyPath:@"@sum.product"];
Mundi
  • 79,884
  • 17
  • 117
  • 140
  • Sorry for being thick, but the relevant section states that fetched properties represent weak, one way relationships, and always return NSArray. How can you create fetched property that multiplies the two fields? – Piotr Farbiszewski Jul 17 '13 at 11:48
  • You are right, the fetched property is not the correct term. See my amended answer. – Mundi Jul 17 '13 at 15:02
  • This is what @Paul.s already suggested and I'm indeed doing it at the moment, but I'm not happy with this solution as it requires all records to be fetched from database to memory. Hardly an ideal solution for thousands of records. I was experimenting with NSExpression and multiply:by: but it seems to be unsupported on iOS :( http://stackoverflow.com/questions/13879025/fetch-aggregate-data-from-nsmanagedobject-using-another-expression-as-argument-t – Piotr Farbiszewski Jul 17 '13 at 19:29
  • It is not as bad as you think. Core Data is very good at reducing the memory footprint behind the scenes. If you want to sum all values you will anyway touch all records, no matter what. CD will optimize to not fetch the fields it does not need (through faulting). – Mundi Jul 17 '13 at 19:33