CODEWITHSUNDEEP
iossqlitecore-data
Przemyslaw
Przemyslaw

Reputation: 186

Core Data equivalent for sqlite query

I use Core Data for an iPhone app. There is "Flight" entity with a "start" and "duration" property. The flights are listed on a paginated view, where I need to sum the duration per page and the duration rollup sum. In native sqlite following solution works:

select sum(pg.zduration) from (select zduration,zstart from zflight order by zstart limit %i,%i) as pg",offset,limit

Now the Question:
 How would I solve that with Core Data, NSExpression, NSExpressionDescription and NSFetchRequest instead of sqlite? Of course, I would not like to load all flight objects in memory...

So I am able to caculate the duration for all flights:

NSFetchRequest *request = [[NSFetchRequest alloc] init];
NSEntityDescription *entity = [NSEntityDescription entityForName:@"Flight" inManagedObjectContext:self.managedObjectContext];
[request setEntity:entity];

[request setResultType:NSDictionaryResultType];

NSSortDescriptor *startSortDescriptor = [[NSSortDescriptor alloc] initWithKey:@"start"
                                                                    ascending:YES];
NSArray *sortDescriptors = [[NSArray alloc] initWithObjects:startSortDescriptor, nil];
[request setSortDescriptors:sortDescriptors];

request.fetchOffset=onPage*pageSize;//does not help, cause offset and limit are applied to the result
request.fetchLimit=pageSize;//does not help, cause offset and limit are applied to the result

NSExpression *keyPathExpression = [NSExpression expressionForKeyPath:@"duration"];    
NSExpression *sumExpression = [NSExpression expressionForFunction:@"sum:" arguments:[NSArray arrayWithObject:keyPathExpression]];

// Create an expression description using the minExpression and returning a date.
NSExpressionDescription *expressionDescription1 = [[NSExpressionDescription alloc] init];    
[expressionDescription1 setName:@"durationSum"];
[expressionDescription1 setExpression:sumExpression];
[expressionDescription1 setExpressionResultType:NSInteger64AttributeType];


[request setPropertiesToFetch:[NSArray arrayWithObjects:expressionDescription1,nil]];

// Execute the fetch.
NSError *error = nil;
NSArray *objects = [self.managedObjectContext executeFetchRequest:request error:&error];
if(error!=nil){
    [NSException raise:@"Sum Page Duration failed" format:@"%@ Error:%@", [[error userInfo] valueForKey:@"reason"],error];        
}

if (objects!=nil && [objects count] > 0) {
    return (NSNumber*)[[objects objectAtIndex:0] valueForKey:@"durationSum"];
}
return 0;

Upvotes: 2

Views: 673

Answers (1)

wixardy
wixardy

Reputation: 35

As you said, the limit and offset set on the fetch request are applied to the result and NSExpression won't work well in this case. You could operate on the returned objects, after they've been offset and limited by the fetch request, using a collection operator rather than NSExpression, e.g.:

NSNumber *durationSum = [objects valueForKeyPath:@"@sum.duration"];

Upvotes: 1

Related Questions