2

Is there any way to GROUP BY core data based on its month then SUM its properties? I have data like this

------------------------
   date    |   amount  
------------------------ 
30-08-2017 |  124000000
28-10-2017 |  124000000
16-10-2017 |  124000000
14-12-2017 |  124000000
20-12-2017 |  124000000
------------------------

I just need to show one latest year, so I created my fetch request like this 

NSDate *now = [NSDate date];
NSDate *oneYearAgo = [now addYears:-1];

NSEntityDescription *entity = [NSEntityDescription entityForName:@"MyTable" inManagedObjectContext:moc];
[request setEntity:entity];

NSPredicate *predicate = [NSPredicate predicateWithFormat:@"(myDate >= %@) AND (myDate <= %@) AND isActive == %@", oneYearAgo, now, @YES];
[request setPredicate:predicate];

NSSortDescriptor *sort = [[NSSortDescriptor alloc] initWithKey:@"myDate" ascending:YES];
[request setSortDescriptors:@[sort]];

NSExpressionDescription* ex = [[NSExpressionDescription alloc] init];
[ex setName:@"totalCost"];
[ex setExpression:[NSExpression expressionWithFormat:@"@sum.amount"]];
[ex setExpressionResultType:NSDecimalAttributeType];

[request setPropertiesToFetch:[NSArray arrayWithObjects:@"myDate", ex, nil]];
[request setPropertiesToGroupBy:[NSArray arrayWithObject:@"myDate"]];
[request setResultType:NSDictionaryResultType ];

NSArray *result = [moc executeFetchRequest:request error:nil];

That's closest solution for what I need, but that grouping still based on day but month, and it doesn't sum its amount.

So I want to get the result like this

Aug 2017 | 124000000
Oct 2017 | 248000000
Dec 2017 | 248000000

Any help would be appreciated. Thank you!

Sonic Master
  • 1,238
  • 2
  • 22
  • 36

1 Answers1

1

You can use the NSFetchResultsContoller with custom section path to group your data based upon the month. Then you can sum all the items in the section to get the required results. Following code snippet can be used to create fetch result controller

NSFetchedResultsController *dateAmountFetchResultsController =
    [[NSFetchedResultsController alloc] initWithFetchRequest:fetchRequest
                                        managedObjectContext:managedObjectContext
                                    sectionNameKeyPath:@"monthIndex" cacheName:@"MyEntity"];

Create a new transient attribute on your DateAmount entity, called "monthIndex". Then create a method on your DateAmount class -(NSString*)monthIndex, to return the correct month number string. @"monthIndex" should be used as the section name key path for your fetched results controller as per above code snippet. In the sort descriptor of the fetch request, use sort descriptor with key @"monthIndex" for sorting the items, as it is a requirement of the NSFetchedResultsController.

PK Bhatia
  • 21
  • 3
  • 1
    Note that you cannot sort on transient properties using the SQLite store. – pbasdf Mar 01 '17 at 13:40
  • Could you please more specific, @pbasdf ? Thank you! – Sonic Master Mar 02 '17 at 02:28
  • pbasf, you are right. So, here sort by date would also work. Sonic, if you want to get the data for latest year only, then you can include your date predicate in the fetch request. Then calculate the sum of all items in section along with above solution. – PK Bhatia Mar 02 '17 at 04:05