Here's my ER schema:
owns is in
user 1 <----> N real_estate N <-----> 1 region
What I want:
- for each user,
- count the unique number of regions
- where the user owns any real estate
The equivalent SQL query:
SELECT user.id AS user_id,
COUNT(DISTINCT real_estate.region_id) AS region_count
FROM user
LEFT JOIN real_estate
ON real_estate.owner_id = user.id
GROUP BY user.id
The tricky part is getting the COUNT DISTINCT work with Core Data and NSExpression.
I was able to perform a non-distinct COUNT using:
let ctx: NSManagedObjectContext = …
let expr = NSExpression(format: "ownedRealEstates.region.regionID.@count")
let desc = NSExpressionDescription()
desc.expression = expr
desc.name = "count"
desc.expressionResultType = .integer64AttributeType
let fr = NSFetchRequest<NSDictionary>(entityName: "User")
fr.resultType = .dictionaryResultType
fr.propertiesToFetch = [
"userID",
desc
]
let rows = try ctx.fetch(fr)
print(rows)
But how would I go about counting only the unique region IDs?