CODEWITHSUNDEEP
ioscore-data
jmacdonagh
jmacdonagh

Reputation: 4241

How can I sort Core Data records before GROUP BY?

Imagine I have this Core Data entity

@objc(SomeEntity)
class SomeEntity: NSManagedObject {
  @NSManaged var date: Date
  @NSManaged var timeZoneIdentifier: String
  @NSManaged var name: String
  @NSManaged var brand: String
}

I want to fetch all rows with distinct name and brand fields, but for each one, I want the latest date and the timeZoneIdentifier for that latest date row.

Here's some example data:

date timeZoneIdentifier name brand
2020-01-01 America/Los_Angeles Name 1 Brand 1
2020-01-02 America/Tiajuana Name 1 Brand 1
2020-01-03 America/Denver Name 1 Brand 2
2020-01-04 America/Phoenix Name 2 Brand 1

I essentially want to fetch .dictionaryResultType results that will look like this:

lastDate lastDateTimeZoneIdentifier name brand
2020-01-02 America/Tiajuana Name 1 Brand 1
2020-01-03 America/Denver Name 1 Brand 2
2020-01-04 America/Phoenix Name 2 Brand 1

Which you could do with this SQL query:

SELECT * FROM (SELECT * FROM SomeEntity ORDER BY date DESC) as sub GROUP BY sub.name, sub.brand;

Can I achieve the same thing with Core Data with minimal queries? I can't find any examples or documentation online trying to do something like this.

I can write a NSFetchRequest that essentially executes this:

SELECT date, timeZoneIdentifier, name, brand FROM SomeEntity GROUP BY name, brand ORDER BY date;

But that orders after the GROUP BY, not before.

I can also write a NSFetchRequest that essentially executes this:

SELECT max(date), name, brand FROM SomeEntity GROUP BY name, brand;

Which almost works only because I wanted the latest date, but of course I lose the timeZoneIdentifier that the row came from. I have written up this example as a gist that you can throw into a Swift playground.

Thanks!

Upvotes: 1

Views: 78

Answers (1)

pbasdf
pbasdf

Reputation: 21536

As you’ve probably discovered, you cannot specify any propertiesToFetch other than the properties you group by, and aggregate functions. Hence you can’t include the timeZoneIdentifier.

However, you can include an NSExpression that represents the evaluated object (see NSExpression.expressionForEvaluatedObject). This will give you the objectID for the object with the max(date) - which you can then fetch to get the other property values.

Upvotes: 2

Related Questions