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.

Answer 1

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.

Answer 2

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.