Subquery on iOS CoreData

Question

I have a CoreData performance problem in my iOS App.

I need execute a query with a subquery clause.

Lets explain the case.

I have a Entity called quota that has groupCode and date attributes. (There are more attributes but it isn't import for now).

For execute my search, it's require two step:

At first, I need select all quota items where groupCode is equals to quotaGroupProductParam (quotaGroupProductParam is a variable. For this example quotaGroupProductParam = 6816)
At last, for each result I need execute a new search in the same entity use as clause date = result.date and groupCode = quotaGroupParam (quotaGroupParam is a variable. For this example quotaGroupParam = 58).

Example:

NSArray *quotasGroupProduct = [[DBContext shared] executeFetchRequest:@"Quota" WithFilter:[NSPredicate predicateWithFormat:@"groupCode=%@", quotaGroupProductParam]];
for (Quota *quota in quotasGroupProduct) {
    NSArray *quotasGroup = [[DBContext shared] executeFetchRequest:@"Quota" WithFilter:[NSPredicate predicateWithFormat:@"groupCode = %@ AND date = %@", quotaGroupParam, quota.date]];
    if([quotasGroup count] > 0 && [quotasRepresentative count] > 0) {
        [quotaDatesArray addObject:quota.date];
    }
}

I'd like change this code to execute just one fetchRequest.

Below a SQL query that do that:

select * from zquota q where zgroupCode = 58 and exists (select 1 from zquota where ZgroupCode = 6816 and zdate = q.zdate);

Anyone could help me?

Thanks!

Yes, I know. However, how can I made a NSPredicate for exists clause? — andercruzbr, Mar 10 '17 at 17:37
Here is the [Link](http://stackoverflow.com/questions/958622/using-nspredicate-to-filter-an-nsarray-based-on-nsdictionary-keys) — antonio081014, Mar 10 '17 at 21:09

score 1 · Answer 1 · answered Mar 11 '17 at 17:04

Currently, each quota in the quotasGroupProduct array results in an additional fetch, which in itself is expensive, but also building and parsing the associated predicate is costly. You can avoid this by changing your predicate to perform these fetches all in one go. Note that to reproduce the same behaviour as your current code, it is easier to perform the fetches in reverse order:

Fetch all Quotas with groupCode = quotaGroupParam.
Extract the dates into an array.
Fetch all Quotas with groupCode = quotaGroupProductParam and with date IN the above array of dates.
Extract the dates into your final array.

(The reason for this relates to duplicates. If there are several Quotas from the first fetch, all with the same date, the intermediate array will contain duplicates of the same date. But the IN clause in the second fetch will ensure only one is included in the final array.)

NSArray *quotasGroup = [[DBContext shared] executeFetchRequest:@"Quota" WithFilter:[NSPredicate predicateWithFormat:@"groupCode=%@", quotaGroupParam]];
// extract the dates...
NSArray *datesOfInterest = [quotasGroup valueForKey:@"date"];
// execute the second fetch
NSArray *quotasGroupProduct = [[DBContext shared] executeFetchRequest:@"Quota" WithFilter:[NSPredicate predicateWithFormat:@"groupCode = %@ AND date IN %@", quotaGroupProductParam, datesOfInterest]];
// extract the dates again
NSArray *finalDatesArray = [quotasGroupProduct valueForKey:@"date"];
if ([quotasRepresentative count] > 0) {
        [quotaDatesArray addObjectsFromArray:finalDatesArray];
}

So, no matter how many items match your predicates, only two fetches are required. I hope this will give you the required performance boost.

Subquery on iOS CoreData

1 Answers1