Conditionally sum and count data using NSExpression expressionForSubquery

Question

I have below table, I want to fetch sum of fileSize field data which isSelectedForSync flag will be YES.

Here is DigitalLibrary Table Attributes

TableName: DigitalLibrary

Attributes and its types:

digitalLibraryID (NSNumber)
fileName (NSString)
connectType (NSString)
fileSize (NSNumber - doubleValue)
isSelectedForSync (NSNumber - BOOL)
isSync (NSNumber - BOOL)

I want to fetch file count whose isSelectedForSync flag isYES. Also require to fetch sum of fileSize whose isSelectedForSync

I am trying below NSExpression, but not getting what I want. Also it gives me error.

    NSFetchRequest *fetchRequest = [[NSFetchRequest alloc] init];

    NSEntityDescription* entityDescription = [NSEntityDescription entityForName:@"DigitalLibrary" inManagedObjectContext:self.managedObjectContext];

    NSDictionary *entityDict = [entityDescription propertiesByName];

    NSPredicate *predicate = [NSPredicate predicateWithFormat:@"isSync == %@ && isSelectedForSync == %@",[NSNumber numberWithBool:NO], [NSNumber numberWithBool:YES]];

    [fetchRequest setPredicate:predicate];
    [fetchRequest setEntity:entityDescription];
    [fetchRequest setResultType:NSDictionaryResultType];
    [fetchRequest setPropertiesToGroupBy:[NSArray arrayWithObjects:[entityDict objectForKey:@"connectType"], nil]];

    //AllPhotoCount
    NSExpression *countExpression = [NSExpression expressionForFunction:@"count:"
                                                              arguments:@[[NSExpression expressionForKeyPath:@"connectType"]]];
    NSExpressionDescription *fileCountExpressionDescription = [[NSExpressionDescription alloc] init];
    [fileCountExpressionDescription setName:@"filesCount"]; // Choose an appropriate name here!
    [fileCountExpressionDescription setExpression:countExpression];
    [fileCountExpressionDescription setExpressionResultType:NSInteger32AttributeType];
//    //Selected Photo Count
    NSExpression *selectedFileExpression = [NSExpression expressionForFunction:@"sum:"
                                                                      arguments:@[[NSExpression expressionForKeyPath:@"isSelectedForSync"]]];
    NSExpressionDescription *selectedFilesExpressionDescription = [[NSExpressionDescription alloc] init];
    [selectedFilesExpressionDescription setName:@"selectionCount"]; // Choose an appropriate name here!
    [selectedFilesExpressionDescription setExpression:selectedFileExpression];
    [selectedFilesExpressionDescription setExpressionResultType:NSInteger32AttributeType];

NSString *shadowVar = @"";
    //Sum Of FileSize which file selected
//    NSExpression *keyPathSelectedFileSizeTotalExpression = [NSExpression expressionForKeyPath:@"fileSize"];
    NSExpression *selectedFileSizeTotalExpression = [NSExpression expressionForFunction:@"sum:"
                                                                              arguments:@[[NSExpression expressionForKeyPath:@"fileSize"]]];

    NSExpression *totalselectedFileSizeExpression = [NSExpression expressionForSubquery:selectedFileSizeTotalExpression usingIteratorVariable:shadowVar predicate:[NSPredicate predicateWithFormat:@"isSelectedForSync == %@",[NSNumber numberWithBool:YES]]];

    NSExpressionDescription *selectedFileSizeTotalExpressionDescription = [[NSExpressionDescription alloc] init];
    [selectedFileSizeTotalExpressionDescription setName:@"selectedFileSizeTotal"];
    [selectedFileSizeTotalExpressionDescription setExpression:totalselectedFileSizeExpression];
    [selectedFileSizeTotalExpressionDescription setExpressionResultType:NSInteger64AttributeType];

 [fetchRequest setPropertiesToFetch:@[[entityDict objectForKey:@"connectType"], fileCountExpressionDescription, selectedFileSizeTotalExpressionDescription]];

    NSLog(@"2nd shadowVar:%@",shadowVar);

    NSError* error = nil;
    NSArray *results = [self.managedObjectContext executeFetchRequest:fetchRequest error:&error];

Above code worked perfectly if I remove 'selectedFileSizeTotalExpressionDescription', but need selectedFileSizeTotalExpressionDescription as mention above.

I am very confused in How to use NSExpression expressionForSubquery method.

Following are data contains into DigitalLibrary table:

+-----------+--------------------+--------------+------------+
|fileSize   |isSelectedForSync   |connectType   |fileName    |
+-----------+--------------------+--------------+------------+
|1000       |0                   |Image         |temp1.png   |
+-----------+--------------------+--------------+------------+
|200        |1                   |Image         |tmp.png     |
+-----------+--------------------+--------------+------------+
|400        |1                   |Image         |tmp23.png   |
+-----------+--------------------+--------------+------------+
|20000      |1                   |Video         |test.mp4    |
+-----------+--------------------+--------------+------------+
|3000       |1                   |Audio         |temp2.mp3   |
+-----------+--------------------+--------------+------------+
|15000      |0                   |Video         |test12.mp4  |
+-----------+--------------------+--------------+------------+
|3500       |0                   |Audio         |temp12.mp3  |
+-----------+--------------------+--------------+------------+

I want below result:

+----------------+-------------------+--------------+----------------+------------------+
|TotalFileSize   |selectedFileSize   |connectType   |totalFileCount  |selectedFileCount |
+----------------+-------------------+--------------+----------------+------------------+
|1400            |600                |Image         |3               |2                 |
+----------------+-------------------+--------------+----------------+------------------+
|35000           |20000              |Video         |2               |1                 |
+----------------+-------------------+--------------+----------------+------------------+
|6500            |3000               |Audio         |2               |1                 |
+----------------+-------------------+--------------+----------------+------------------+

may be my question is difficult to understand. I need group by and where condition both in single NSExpression. e.g. group by fileCount which have selection flag is YES. Thanks, Punita — Punita, Jun 17 '14 at 12:10
Let me explain my question in simple way: I want to execute below SQL query using NSFetchRequest: **SELECT SUM(zfileSize), zconnectType, (SELECT SUM(zfileSize) FROM zDigitalLibrary as dl2 WHERE dl1.zconnectType = dl2.zconnectType and ZISSELECTEDFORSYNC = 1 GROUP BY zconnectType ) AS selectedFileSize FROM zDigitalLibrary as dl1 GROUP BY zconnectType** WILL IT POSSIBLE??? — Punita, Jul 11 '14 at 10:39

score 0 · Answer 1 · edited Sep 04 '18 at 09:30

  NSFetchRequest *fetchRequest = [NSFetchRequest fetchRequestWithEntityName:NSStringFromClass([DigitalLibrary class])];//
    NSPredicate * imageTypePredicate = [NSPredicate predicateWithFormat:@"connectType ==[c] Image"];

    [fetchRequest setPredicate:imageTypePredicate];

    NSArray * imageTypeFilteredArray = [context executeFetchRequest:fetchRequest error:nil];

    NSArray * imageFileSizeArray = [imageTypeFilteredArray valueForKey:@"fileSize"];

    double totalValue = 0.0;

    for (NSNumber * eachFileSize in imageFileSizeArray)
    {
        totalValue += [eachFileSize doubleValue];
    }

    NSPredicate * predicate = [NSPredicate predicateWithFormat:@"isSelectedForSync == YES"];
    NSArray * selectedImageFileSizeArray = [imageTypeFilteredArray filteredArrayUsingPredicate:predicate];


    double totalSelectedValue = 0.0;

    for (NSNumber * eachFileSize in selectedImageFileSizeArray)
    {
        totalSelectedValue += [eachFileSize doubleValue];
    }

Similarly can do for video and audio.

your code will give total fileSize but I want no. of file count, total size of group by file-type. For example, as mentioned in DigitalLibrary table, I want total fileSize, file count as per file type as mentioned in table-result. — Punita, Dec 16 '14 at 10:16

Conditionally sum and count data using NSExpression expressionForSubquery

1 Answers1