How to define a NSPredicate which specifies a having max(x) clause using group by?

Question

I am having trouble creating a valid NSPredicate within an iOS-CoreData context. The table contains all messages of all buddies. I need to get the newest message of each buddy:

I have tried using DBVisualizer and I need the following SQL (which works fine)

    /* for testing with a db tool like DbVisualizer
     select ZBAREJIDSTR,ZBODY, ZTIMESTAMP
     from ZXMPPMESSAGEARCHIVING_MESSAGE_COREDATAOBJECT
     group by ZBAREJIDSTR
     having max(ZTIMESTAMP)
     order by ZTIMESTAMP desc;
     */

The following code fails with "Unable to parse the format string" and I could not find an example which worked for me.

    - (NSFetchedResultsController *)fetchedResultsController {
if (fetchedResultsController == nil)    {

    NSManagedObjectContext *moc = [[Utilities chatManagerFromAppDelegate] managedObjectContext_messageArchiving];

    NSEntityDescription *entity = [NSEntityDescription entityForName:@"XMPPMessageArchiving_Message_CoreDataObject"
                                              inManagedObjectContext:moc];

    NSFetchRequest *fetchRequest = [[NSFetchRequest alloc] init];
    NSAttributeDescription* bodyDesc = [entity.attributesByName objectForKey:@"body"];
    NSAttributeDescription* barJidStrDesc = [entity.attributesByName objectForKey:@"barJidStr"];
    NSAttributeDescription* timestampDesc = [entity.attributesByName objectForKey:@"timestamp"];

    NSPredicate *predicateMaxTimestamp = [NSPredicate predicateWithFormat:@"@max.timestamp"];

    NSSortDescriptor *sortDescriptorTimestampDesc = [[NSSortDescriptor alloc] initWithKey:@"timestamp" ascending:NO];
    NSArray *sortDescriptors = [NSArray arrayWithObjects:sortDescriptorTimestampDesc, nil];

    [fetchRequest setEntity:entity];
    [fetchRequest setPropertiesToFetch:[NSArray arrayWithObjects:barJidStrDesc, bodyDesc, timestampDesc, nil]];
    [fetchRequest setPropertiesToGroupBy:[NSArray arrayWithObject:bodyDesc]];
    [fetchRequest setHavingPredicate: predicateMaxTimestamp];
    [fetchRequest setSortDescriptors:sortDescriptors];
    [fetchRequest setResultType:NSDictionaryResultType];
    [fetchRequest setFetchBatchSize:10];
    fetchedResultsController = [[NSFetchedResultsController alloc] initWithFetchRequest:fetchRequest
                                                                   managedObjectContext:moc
                                                                     sectionNameKeyPath:nil
                                                                              cacheName:nil];
    ....

Actually I would also appreciate other alternatives, because I need updates of the underlaying model ([fetchedResultsController setDelegate:self];) which is not working with a NSDictionaryResultType (but NSDictionaryResultType is necessary for with a group by).

Could you try `@"timestamp.@max"` instead of `@"@max.timestamp"` for the `predicateMaxTimestamp` ? — Martin R, Jan 09 '13 at 18:41
No, same error message: 'Unable to parse the format string "timestamp.@max"'. — theguy, Jan 09 '13 at 18:44
Can you describe the entities, attributes and relationships more detailed? — Martin R, Jan 09 '13 at 18:46
The data model is defined here: [XMPPMessageArchiving](https://github.com/robbiehanson/XMPPFramework/blob/master/Extensions/XEP-0136/CoreDataStorage/XMPPMessageArchiving.xcdatamodeld/XMPPMessageArchiving.xcdatamodel/contents) and I am working with the second entity XMPPMessageArchiving_Message_CoreDataObject — theguy, Jan 09 '13 at 18:49
I think [this](http://stackoverflow.com/a/10978366/1354100) answers the question — Bejmax, Jan 09 '13 at 18:52
I already thought about reading only messages up to the last week (sort it by timestamp) and handling the group/having manually. This way would also get updates of the underlaying model (which are the most interesting messages anyway). — theguy, Jan 09 '13 at 18:54

Mundi · Answer 1 · 2013-01-10T13:25:54.353

0

First, if you want to "group by", you need to specify the fetched results controller's sectionNameKeyPath which should be "barJidStr".

Second, the correct syntax for the max timestamp predicate format string is
@"ANY @max.timestamp".

Third, there is a discrepancy between your SQL and your attribute names. If you attribute is defined as "barJidStr", the raw SQL should be "ZBARJIDSTR", not "ZBAREJIDSTR".

edited Jan 10 '13 at 13:25

answered Jan 09 '13 at 23:16

Mundi

79,884
17
117
140

Thanks for your answer, but I only want one section. That's way I passed nil.My above code fails 10 lines above saying that the NSPredicate is wrong. The missing e in ZBAREJIDSTR is a bug. Thanks about that – theguy Jan 10 '13 at 08:02

How to define a NSPredicate which specifies a having max(x) clause using group by?

1 Answers1