Complex CoreData query (SQL syntax wanted as well)?

I have 2 entities:

Performance.start (NSDate)    Location.coordinates
Performance.end   (NSDate)    Location.name_extern
Performance.location   <<-->  Location.performances

I have a NSFetchedResultsController for my UITableView set up with the following code:

[[NSFetchedResultsController alloc] initWithFetchRequest:fetchRequest 
                                    managedObjectContext:self.managedObjectContext 
                                      sectionNameKeyPath:@"location.name_extern" 
                                               cacheName:nil];

So the data is divided into sections by locations (their names).

Now I have a variable selectedTime of type NSDate and want to fetch (with a single fetch if possible) the following:

1. Only 2 Performances per Location:
   • The Performance that is running (selectedTime BETWEEN {start, end})
   • The Performance that will start after the one that is running at the selected time

I'm really lost here :-/ Even if you could just give me the SQL statement for such a fetch/query I would at least know which d开发者_JAVA技巧irection to head..

Using SQL, you could try something like this:

SELECT *
FROM Performance p, (
    SELECT *
    FROM Performance
    WHERE start <= selectedTime AND end >= selectedTime
    ORDER BY start ASC
    LIMIT 1
) AS p1
WHERE p.start >= p1.start
ORDER BY p.start ASC
LIMIT 2

Although you do have to decide how to handle the case where there is no performance running at that moment.

With Core Data, you could simply order them by start date (ASC), with start <= selectedTime, return 2 results, and (in code?) check whether or not the first result's end date is >= selectedTime.

Duh.. I think I found the final solution ..

The fetch is pretty simple:

NSEntityDescription *entity = [NSEntityDescription 
                               entityForName:@"Performance" inManagedObjectContext:self.managedObjectContext];
[fetchRequest setEntity:entity];

NSSortDescriptor *sort1 = [[NSSortDescriptor alloc] 
                          initWithKey:@"location.name_extern" ascending:YES];
NSSortDescriptor *sort2 = [[NSSortDescriptor alloc] 
                           initWithKey:@"start" ascending:YES];
[fetchRequest setSortDescriptors:[NSArray arrayWithObjects:sort1,sort2,nil]];

[fetchRequest setFetchBatchSize:20];

NSFetchedResultsController *theFetchedResultsController = 
[[NSFetchedResultsController alloc] initWithFetchRequest:fetchRequest 
                                    managedObjectContext:self.managedObjectContext 
                                      sectionNameKeyPath:@"location.name_extern" 
                                               cacheName:nil];

With the following predicate:

NSPredicate *predicate = [NSPredicate predicateWithFormat:@"end > %@",selectedTime];

[fetchRequest setPredicate:predicate];

And here's how I solved the "only 2 per location/section" part:

- (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section
{
    id <NSFetchedResultsSectionInfo> sectionInfo = [[self.fetchedResultsController sections] objectAtIndex:section];
    if (selectedTime == nil)
    {
        return [sectionInfo numberOfObjects];
    }
    else
    {
        if ([sectionInfo numberOfObjects] > 2)
        {
            return 2;
        }
        else
        {
            return [sectionInfo numberOfObjects];
        }
    }
}

Thanks for trying though André!