NSPredicate to get the max value of a property under a given value

Question

I have an entity Update which has two attributes: date and amount. Suppose I have these objects in Core Data:

|       Date | Amount |
|------------+--------|
| 2020-01-01 |    100 |
| 2020-01-05 |    200 |
| 2020-01-10 |    300 |
| 2020-01-15 |    400 |

My purpose is to get the object with the latest date before a given date. For example, given a date of 2020-01-12, the result should be the object whose date is 2020-01-10. I wonder if it's possible to do this with a single NSPredicate?

I tried the following but it doesn't work because max() is not aware of the other constraints (see a dicussion here)

request.predicate = NSPredicate(format: "date < %@ AND date == max(date)", given_date as CVarArg)

I also considered SUBQUERY becuase that's the approach to do it in SQL. But unfortunately it seems that SUBQUERY in Core Data is supposed to be used with two tables (it requires an explicit collection argument).

I have read NSExpression document, but as far as I can tell, it's impossible to define an NSExpression to do it either (the above NSPredicate format string I tried is actually a NSExpression in string format so they use the same max()).

Does that mean I have to fetch multiple entries with NSPredicate(format: "date < %@", given_date as CVarArg) first and then run a second predicate to get the latest one? But isn't this inefficient because it fetch multiple entries although I only need one?

Am I missing something? Thanks for any suggestions.

Note: I considered setting fetchLimit to 1. But this doesn't work in my cases because there may be multiple objects with the same date and I want to get all of them if their dates meet the requirement.

score 2 · Accepted Answer · answered Jan 23 '20 at 13:50

It is possible to combine the two fetches into one. Rather than "running" the first fetch, pass it (as a NSFetchRequestExpression) to the main fetch request:

func fetchUpdates(_ date: Date) {
    let request = NSFetchRequest<NSFetchRequestResult>(entityName: "Update")
    request.predicate = NSPredicate(format: "date <= %@", date as CVarArg)
    let expressionDescription = NSExpressionDescription()
    expressionDescription.expression = NSExpression(format: "@max.date")
    expressionDescription.name = "maxdate"
    expressionDescription.expressionResultType = .dateAttributeType
    request.propertiesToFetch = [expressionDescription]
    request.resultType = NSFetchRequestResultType.dictionaryResultType
    // Identical up to here, then:
    let contextExpression = NSExpression(forConstantValue: self.managedObjectContext)
    let fetchExpression = NSExpression(forConstantValue: request)
    let fre = NSFetchRequestExpression.expression(forFetch: fetchExpression, context: contextExpression, countOnly: false)
    let mainFetch = NSFetchRequest<NSFetchRequestResult>(entityName: "Update")
    mainFetch.predicate = NSPredicate(format: "date == %@", fre)
    let results = try! self.managedObjectContext!.fetch(mainFetch)
    ....

Be aware that the Date attribute type includes time, so Updates occurring on the same DAY might have a different date.

Thanks! You saved my day. So I google about it but find very few discussion (I understand its use, I'm just curious how often people use it). Seems it's not used often? But I think my question is a typical scenario...maybe this suggests that most people just pull all entries and process it in App (perhaps because the performance difference are unnoticeable in most cases). — rayx, Jan 23 '20 at 14:38
If you don't mind, may I ask another question? (It's a bit general so I'm not sure if it's a good idea to submit a separate question). In above example, I can use either fetch request or relationship in my App code. Both are simple to implement. Do you think which approach is better in term of performance? Or is that a case by case decision? I'm a bit confused because I read in a few places (e.g, a book by objc.io) that fetch requests should be avoided as much as possible, but on the other hand, I have the impression that in most SO discussions people seem to use fetch requests. — rayx, Jan 23 '20 at 14:54
I'm afraid predicting CoreData performance (fetch v code) is too dependent on individual circumstances, so really it's a matter of trying it out. CoreData is pretty well optimised by Apple, so for many situations running a fetch has limited overhead. Nevertheless, it's worth fetching once and holding the results in memory if you can. For example, if you are populating a tableView, fetch once when the view is loaded (or use a fetched results controller) - certainly don't fetch on each call to `cellForRowAt` (which I've seen done before)! — pbasdf, Jan 23 '20 at 17:37

score 0 · Answer 2 · answered Jan 23 '20 at 13:23

I work out something like this:

func fetchUpdates(_ date: Date) {
        let request = NSFetchRequest<NSFetchRequestResult>(entityName: "Update")
        request.predicate = NSPredicate(format: "date <= %@", date as CVarArg)
        let expressionDescription = NSExpressionDescription()
        expressionDescription.expression = NSExpression(format: "@max.date")
        expressionDescription.name = "maxdate"
        expressionDescription.expressionResultType = .dateAttributeType
        request.propertiesToFetch = [expressionDescription]
        request.resultType = NSFetchRequestResultType.dictionaryResultType
        // Run the request, this will return the the date
        ...
        // Then construct a second fetch request to get the object(s) with the date
        ...

}

The function issues two fetch requests. If I understand it correctly, the first request still gathers multiple entries on SQLite layer and performs aggregation operation on Core Data layer. I'm not sure if this is better than the usual approach which issues only one fetch request but passes all entries to app layer and let app to search them (e.g., using NSPredicate) for the entry with max date.

NSPredicate to get the max value of a property under a given value

2 Answers2