Count Queries in Core Data: The Master Guide

Published on

Get weekly handpicked updates on Swift and SwiftUI!

In Core Data, developers often need to deal with counting records, such as using count as a predicate or sorting condition. This article will introduce several methods for querying and using count in Core Data, suitable for different scenarios.

1. Querying count data through countResultType

This method is the most direct way to query the number of records. By setting the resultType of NSFetchRequest to countResultType, the count result of the data can be obtained directly.

Swift
let fetchRequest = NSFetchRequest<NSNumber>(entityName: "Item")
fetchRequest.resultType = .countResultType
let count = (try? viewContext.fetch(fetchRequest).first)?.intValue ?? 0
print(count)
/*
 CoreData: sql: SELECT COUNT(*) FROM ZITEM
 CoreData: annotation: total count request execution time: 0.0002s for count of 190.
 190
 */

The comments in the previous code are the SQL commands corresponding to the Core Data statements (generated using com.apple.CoreData.SQLDebug 1). For specific setup instructions, please refer to Core Data with CloudKit: Troubleshooting.

2. Querying count data using the count method on the managed object context

A more convenient version of Method 1. Call the count method provided by the managed object context, which returns a value of type Int.

Swift
let fetchRequest = NSFetchRequest<Item>(entityName: "Item")
let count = (try? viewContext.count(for: fetchRequest)) ?? 0
print(count)
/*
 CoreData: sql: SELECT COUNT(*) FROM ZITEM
 CoreData: annotation: total count request execution time: 0.0002s for count of 190.
 190
 */

Method 2 corresponds to exactly the same SQL command as method 1.

When only needing to obtain the count (not concerned with the specific content of the data), methods 1 and 2 are good choices.

3. Obtaining Count Data from Result Sets

Sometimes, after obtaining a result set, it is necessary to view the count of the data set. This can be achieved by directly utilizing the count method of the set.

Swift
let fetchRequest = NSFetchRequest<Item>(entityName: "Item")
fetchRequest.predicate = NSPredicate(format: "%K > %@", #keyPath(Item.timestamp), Date.now as CVarArg)
let items = (try? viewContext.fetch(fetchRequest)) ?? []
let count = items.count
print(count)
/*
 CoreData: sql: SELECT 0, t0.Z_PK, t0.Z_OPT, t0.ZTIMESTAMP FROM ZITEM t0 WHERE  t0.ZTIMESTAMP > ?
 CoreData: annotation: sql connection fetch time: 0.0001s
 CoreData: annotation: total fetch execution time: 0.0002s for 0 rows.
 */

Calling count does not trigger lazy loading of data.

In SwiftUI, the results set obtained using @FetchRequest can also be used in the above way.

If fetchLimit is set, the correct count result may not be obtained. After fetchLimit is set, only results up to the specified number will be returned.

4. Obtaining the count data for a single record’s one-to-many relationship

If you have set up a one-to-many relationship in your object model, calling the count method on the relationship property will allow you to obtain the number of objects in that relationship for a single record.

Swift
let fetchRequest = NSFetchRequest<Item>(entityName: "Item")
let items = (try? viewContext.fetch(fetchRequest)) ?? []
let firstItemTagsCount = items.first?.attachments?.count ?? 0 // Counting the relationship will cause this record to be populated
print(firstItemTagsCount)
/*
 CoreData: sql: SELECT 0, t0.Z_PK, t0.Z_OPT, t0.ZTIMESTAMP FROM ZITEM t0
 CoreData: annotation: sql connection fetch time: 0.0002s
 CoreData: annotation: total fetch execution time: 0.0004s for 190 rows.
 CoreData: sql: SELECT 0, t0.Z_PK FROM Z_1TAGS t1 JOIN ZTAG t0 ON t0.Z_PK = t1.Z_2TAGS WHERE t1.Z_1ITEMS = ?
 CoreData: annotation: sql connection fetch time: 0.0001s
 CoreData: annotation: total fetch execution time: 0.0001s for 0 rows.
 CoreData: annotation: to-many relationship fault "tags" for objectID 0xa7ab2d44ebb9106e <x-coredata://0783522F-1851-4BC7-AE0D-AB4C83489E8B/Item/p1> fulfilled from database.  Got 0 rows
 0
 */

The above code will obtain the count data for the attachments one-to-many relationship in the first record. In this example, calling the count method will cause Core Data to populate the first record with data, thus removing it from a lazy state.

You can adjust the timing of prefetching by setting relationshipKeyPathsForPrefetching. The following code won’t fill in the data even if the count method is called.

Swift
let fetchRequest = NSFetchRequest<Item>(entityName: "Item")
fetchRequest.relationshipKeyPathsForPrefetching = ["attachments"]
let items = (try? viewContext.fetch(fetchRequest)) ?? []
let firstItemTagsCount = items.first?.attachments?.count ?? 0 // Count the number of relationships. Preloading relationships won't cause this record to be populated.
print(firstItemTagsCount)
/*
 CoreData: sql: SELECT 0, t0.Z_PK, t0.Z_OPT, t0.ZATTACHMENTCOUNT, t0.ZBIRTHOFYEAR, t0.ZTIMESTAMP FROM ZITEM t0
 CoreData: annotation: sql connection fetch time: 0.0003s
 CoreData: annotation: Bound intarray _Z_intarray0
 CoreData: annotation: Bound intarray values.
 CoreData: sql: SELECT 0, t0.Z_PK, t0.Z_OPT, t0.ZTITLE, t0.ZITEM FROM ZATTACHMENT t0 WHERE  t0.ZITEM IN (SELECT * FROM _Z_intarray0)  ORDER BY t0.ZITEM
 CoreData: annotation: sql connection fetch time: 0.0021s
 CoreData: annotation: total fetch execution time: 0.0024s for 1581 rows.
 CoreData: annotation: Prefetching with key 'attachments'.  Got 1581 rows.
 CoreData: annotation: total fetch execution time: 0.0053s for 190 rows.
 */

Because during the fetch process, the NSManagedObjectID of the relationship data specified in relationshipKeyPathsForPrefetching has been extracted together.

5. Use count of many-to-many relationships to set predicates

The count of many-to-many relationships is often used as a condition for predicates. The following code will only return results where the count of attachments (a many-to-many relationship) is greater than 2.

Swift
let fetchquest = NSFetchRequest<Item>(entityName: "Item")
fetchquest.predicate = NSPredicate(format: "attachments.@count > 2")
let results = try? viewContext.fetch(fetchquest)
print(results?.count)
/*
 CoreData: sql: SELECT 0, t0.Z_PK, t0.Z_OPT, t0.ZTIMESTAMP FROM ZITEM t0 WHERE (SELECT COUNT(t1.Z_PK) FROM ZATTACHMENT t1 WHERE (t0.Z_PK = t1.ZITEM) ) > ?
 CoreData: annotation: sql connection fetch time: 0.0003s
 CoreData: annotation: total fetch execution time: 0.0006s for 144 rows.
 Optional(144)
 */

Expressions like attachments.@count can only be used as predicates and cannot be used as sorting conditions.

6. Recording count data for many-to-many relationships through derived property

Derived attributes provide the ability to pre-store count results for many-to-many relationships. Derived attributes will automatically populate data according to the settings when data changes (create, update, delete). When there is a frequent need to read count data, derived attributes are an excellent solution.

https://cdn.fatbobman.com/image-20211025183247335.png

For complete instructions on using derived and transient attributes in Core Data, please refer to How to use Derived and Transient Properties in Core Data.

7. Sorting by the count recorded in derived property

The attachmentCount in the following code is a derived property of Item, which records the count data of the attachments many-to-one relationship.

Swift
let fetchquest = NSFetchRequest<Item>(entityName: "Item")
fetchquest.sortDescriptors = [NSSortDescriptor(keyPath: \Item.attachmentCount, ascending: true)]
let items = (try? viewContext.fetch(fetchquest)) ?? []
print(items.count)
/*
 CoreData: sql: SELECT 0, t0.Z_PK, t0.Z_OPT, t0.ZATTACHMENTCOUNT, t0.ZBIRTHOFYEAR, t0.ZTIMESTAMP FROM ZITEM t0 ORDER BY t0.ZATTACHMENTCOUNT
 CoreData: annotation: sql connection fetch time: 0.0002s
 CoreData: annotation: total fetch execution time: 0.0004s for 190 rows.
 190
 */

Implementing the requirements of Method Four will be much simpler by pre-storing count data through derived property.

8. Recording count data using willSave

Derived attributes are very convenient to use, but the pre-set methods are limited. By overriding the willSave method of the managed object, you can gain more control.

For example, the following code will only record the count value of attachments with title length greater than 10:

Swift
extension Item{
    public override func willSave() {
        super.willSave()
        let count = attachments?.allObjects.filter{
            (($0 as! Attachment).title?.count ?? 0 ) > 10
        }.count ?? 0
        setPrimitiveValue(Int32(count), forKey: "manualCount")
    }
}

In the willSave method, we can adjust or log data according to business needs. Complex logic will have a certain impact on the efficiency of data modification.

When adding derived attributes or willSave methods to a CoreData database that is already in use, the addition of new attributes to existing data needs to be handled through mapping or migration code.

9. Query the count data of all records in a certain one-to-many relationship

When we need to calculate the total value of a one-to-many relationship for all records that match the set predicate, without using derived attributes or willSave, we can use the following code:

Swift
let fetchRequest = NSFetchRequest<NSFetchRequestResult>(entityName: "Item")
let expressionDescription = NSExpressionDescription()
fetchRequest.resultType = .dictionaryResultType

let name = "totalAttachment"
expressionDescription.name = name
expressionDescription.resultType = .integer32

let attachmentCount = NSExpression(format: "attachments")
let expression = NSExpression(forFunction: "count:", arguments: [attachmentCount])

expressionDescription.expression = expression
fetchRequest.propertiesToFetch = [expressionDescription]
let result = (try? viewContext.fetch(fetchRequest).first as? [String: Int32]) ?? [:]
print(result[name] ?? 0)

/*
Alternatively, we can directly query from the Attachment side:
CoreData: sql: SELECT COUNT( t1.Z_PK) FROM ZITEM t0 LEFT OUTER JOIN ZATTACHMENT t1 ON t0.Z_PK = t1.ZITEM
CoreData: annotation: sql connection fetch time: 0.0002s
CoreData: annotation: total fetch execution time: 0.0002s for 1 rows.
Optional([{
    totalAttachment = 839;
}])
*/

The above code can be described as follows:

  • Set resultType to dictionaryResultType.
  • NSExpressionDescription will be used in propertiesToFetch, its name and result will appear in the returned dictionary.
  • NSExpression is used in many scenarios in Core Data, for example, in the Data Model Editor, many settings are completed through NSExpression.
  • The count method is used by NSExpression in this method.
  • The returned result is an array of dictionaries. Depending on propertiesToFetch, type conversion should be performed on the Value of the dictionary.

By using this method, SQLite will internally count the attachments.

10. Use derived property to query the count data of all records in a one-to-many relationship.

If a derived property for storing count has been set up for the one-to-many relationship, the following code can be used to achieve the requirement in method nine.

Swift
let fetchquest = NSFetchRequest<NSFetchRequestResult>(entityName: "Item")
fetchquest.resultType = .dictionaryResultType
let expressDescription = NSExpressionDescription()
let name = "totalAttachment"
expressDescription.name = name
expressDescription.resultType = .integer32

let attachmentCount = NSExpression(format: "%K", #keyPath(Item.attachmentCount))
let express = NSExpression(forFunction: "sum:", arguments: [attachmentCount])

expressDescription.expression = express
fetchquest.propertiesToFetch = [expressDescription]
let result = (try? viewContext.fetch(fetchquest).first as? [String: Int32]) ?? [:]
print(result[name] ?? 0)
/*
CoreData: sql: SELECT total( t0.ZATTACHMENTCOUNT) FROM ZITEM t0
CoreData: annotation: sql connection fetch time: 0.0001s
CoreData: annotation: total fetch execution time: 0.0002s for 1 rows.
1581
Faster than the summing method above
*/

Because the pre-stored count value exists, the sum method is used in NSExpression.

Compared to method 9, the query efficiency of method 10 is higher.

11. Query count data after grouping

In some cases, we need to group data and then get the count of each group. We can use propertiesToGroupBy to let SQLite do this for us.

For example, if an Item has a birthOfYear property that stores year data (Int), the following code groups the data by birthOfYear and returns the count for each group:

Swift
let fetchquest = NSFetchRequest<NSFetchRequestResult>(entityName: "Item")
fetchquest.propertiesToGroupBy = ["birthOfYear"]
fetchquest.sortDescriptors = [NSSortDescriptor(keyPath: \Item.birthOfYear, ascending: false)]
fetchquest.resultType = .dictionaryResultType
let expressDescription = NSExpressionDescription()
expressDescription.resultType = .integer32
let name = "count"
expressDescription.name = name
let year = NSExpression(forKeyPath:\Item.birthOfYear)
let express = NSExpression(forFunction: "count:", arguments: [year])
expressDescription.expression = express
fetchquest.propertiesToFetch = ["birthOfYear",expressDescription]
let results = (try? viewContext.fetch(fetchquest) as? [[String:Any]]) ?? []
print(results)
/*
 CoreData: sql: SELECT t0.ZBIRTHOFYEAR, COUNT( t0.ZBIRTHOFYEAR) FROM ZITEM t0 GROUP BY  t0.ZBIRTHOFYEAR
 CoreData: annotation: sql connection fetch time: 0.0002s
 CoreData: annotation: total fetch execution time: 0.0003s for 5 rows.

 [["birthOfYear": 2000, "count": 32], ["birthOfYear": 2001, "count": 36], ["count": 42, "birthOfYear": 2002], ["birthOfYear": 2003, "count": 44], ["birthOfYear": 2004, "count": 36]]
 */

As this implementation relies on SQLite’s internal implementation, it will be very efficient.

When there is a similar need in the business logic, consider pre-setting properties that are suitable for grouping for managed objects. The contents of the property can also be handled through derivation or willSave.

12. Using grouped count data as a filtering condition

If you want to filter the result set obtained in method eleven, in addition to manipulating the result array through code, using Core Data’s support for having and directly processing it in SQLite will be more efficient. The following code will only return results with a count greater than 40.

Swift
let fetchquest = NSFetchRequest<NSFetchRequestResult>(entityName: "Item")
fetchquest.propertiesToGroupBy = ["birthOfYear"]
fetchquest.resultType = .dictionaryResultType

let expressDescription = NSExpressionDescription()
expressDescription.resultType = .integer32
let name = "count"
expressDescription.name = name
let year = NSExpression(forKeyPath:\\Item.birthOfYear)
let express = NSExpression(forFunction: "count:", arguments: [year])
expressDescription.expression = express

fetchquest.propertiesToFetch = ["birthOfYear",expressDescription]
// Create variable
let countVariableExpr = NSExpression(forVariable: "count")
// Filter the groupby result again
fetchquest.havingPredicate = NSPredicate(format: "%@ > 40",countVariableExpr)
let results = (try? viewContext.fetch(fetchquest) as? [[String:Any]]) ?? []
print(results)
/*
 CoreData: sql: SELECT t0.ZBIRTHOFYEAR, COUNT( t0.ZBIRTHOFYEAR) AS __var0 FROM ZITEM t0 GROUP BY  t0.ZBIRTHOFYEAR HAVING __var0 > ?
 CoreData: annotation: sql connection fetch time: 0.0002s
 CoreData: annotation: total fetch execution time: 0.0002s for 2 rows.
 [["birthOfYear": 2002, "count": 42], ["birthOfYear": 2003, "count": 44]]
 */

As the count in the result set is not a managed object property, it cannot be directly used in NSPredicate. This problem can be solved by using NSExpression(forVariable: "count").

Directly processing in SQLite will be more efficient than manipulating the result set array of Method Eleven in code.

Conclusion

There is no difference in the superiority or inferiority of the methods introduced in this article. Each method has its own suitable scenario. Only by mastering more basic knowledge and considering the overall situation can an efficient solution be achieved.

Weekly Swift & SwiftUI insights, delivered every Monday night. Join developers worldwide.
Easy unsubscribe, zero spam guaranteed