Get top 1 from distinct attribute on CoreData

Question

Hi I have a table with the format shown below.

I want to build an history view, so I need the last messages from distinct users, ordered by timestamp!

+---+-------------------+-------------------+---------------+ | | Username | Message | Timestamp | +---+-------------------+-------------------+---------------+ | 1 | John | Hello | 486380161.723 | | 2 | Mark | Spreadsheet | 486380264.723 | | 3 | John | FYI | 486380366.723 | | 4 | John | Bye | 486557497.271 | | 5 | Mark | How are you? | 486557597.274 | | 6 | Mario | What? | 486558597.274 | +---+-------------------+-------------------+---------------+

This is what my result should be.

+---+-------------------+-------------------+---------------+ | | Username | Message | Timestamp | +---+-------------------+-------------------+---------------+ | 6 | Mario | What? | 486558597.274 | | 5 | Mark | How are you? | 486557597.274 | | 4 | John | Bye | 486557497.271 | +---+-------------------+-------------------+---------------+

For now, I'm getting all distinct username, iterating each one and querying the messages for that username, ordered by timestamp, with limit(1).

I'm not happy with this solution so anyone can help me on a better one?

Thanks, Mário

Answer 1

It's possible to do it in two fetches, with one caveat that I'll mention when I get to it.

The first fetch gets the usernames and the most recent timestamp for each:

    let maxTimestampRequest = NSFetchRequest(entityName: "Entity")
    maxTimestampRequest.resultType = .DictionaryResultType

    let maxTimestampExpression = NSExpression(format: "max:(timestamp)")
    let maxTimestampExpressiondescription = NSExpressionDescription()
    maxTimestampExpressiondescription.name = "maxTimestamp"
    maxTimestampExpressiondescription.expression = maxTimestampExpression
    maxTimestampExpressiondescription.expressionResultType = .DoubleAttributeType

    maxTimestampRequest.propertiesToFetch = ["username", maxTimestampExpressiondescription]
    maxTimestampRequest.propertiesToGroupBy = ["username"]

Execute that fetch and you get an array of dictionaries. Each dictionary contains a username and the most recent timestamp for that username:

Optional([{
    maxTimestamp = "486557497.271";
    username = John;
}, {
    maxTimestamp = "486558597.274";
    username = Mario;
}, {
    maxTimestamp = "486557597.274";
    username = Mark;
}])

Getting the complete records requires a second fetch. If the results of the previous fetch are in an array called results,

    var predicates = [NSPredicate]()
    for maxTimestampInfo in results! {
        let username = maxTimestampInfo["username"]!
        let timestamp = maxTimestampInfo["maxTimestamp"]!
        let partialPredicate = NSPredicate(format: "username=%@ and timestamp=%@", argumentArray:[ username, timestamp ])
        predicates.append(partialPredicate)
    }
    let completePredicate = NSCompoundPredicate(orPredicateWithSubpredicates: predicates)

    let fetch = NSFetchRequest(entityName: "Entity")
    fetch.predicate = completePredicate

Execute that fetch and you get the full managed objects that match your requirements.

The caveat is that the predicate in the second fetch could potentially be very large, depending on the number of users you have.