Core Data equivalent for sqlite query

Question

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

So on first page, with a page size of 5, I get duration sum and same rollup duration with offset=0 and limit=5.
On second page, I get the duration sum with offset=5 and limit=5. The rollup sum with offset=0 and limit=10. And so on..

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;

wixardy · Accepted Answer · 2012-01-02 03:14:39Z

1

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

answered Jan 2, 2012 at 3:14

wixardy

351 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Core Data equivalent for sqlite query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related