How to Fetch Associated Records with Aggregated Counts Using GRDB and ValueObservation?

[pedrommcarrasco]

Problem:

I’m migrating from Core Data to GRDB and have encountered a blocker that Core Data seems to handle automatically. While I’ve read much of the documentation, I haven’t been able to piece together a solution for my specific use case.

I’ve successfully implemented the following code to fetch an Employee and their associated Company:

let request = Employee.including(optional: Employee.company)

struct EmployeeInfo: FetchableRecord, Decodable {
    var employee: Employee
    var company: Company? // the optional associated company
}
let employeeInfos: [EmployeeInfo] = try EmployeeInfo.fetchAll(db, request)

However, I need to expand this example to match a more complex scenario:

• An Employee can work for a Company.
• A Company has two associations: hasMany(Employee) and hasMany(Investor).

Goal:

While performing a ValueObservation, I want to fetch not only the employee and the company associated with it, but also the total count of employees and investors associated with his company (summed together).

[groue]

Hello @pedrommcarrasco,

GRDB is not currently able to fetch an associated record along with aggregates. When you run a request like the one below, you face a "Not implemented" fatalError:

let request = Employee
    .including(optional: Employee.company
        .annotated(with: Company.employees.count))

Until the library learns to run such a request, you can do it in three steps:

1. Fetch employees with their company
2. Fetch the counts for those companies
3. Merge both results together

ValueObservation will deal with it just fine.

Sample code (you can paste and run):

// The base records
struct Employee: Codable, FetchableRecord, PersistableRecord {
    static let company = belongsTo(Company.self)
    var id: Int64
    var companyId: Int64?
}
struct Company: Codable, Identifiable, FetchableRecord, PersistableRecord {
    static let employees = hasMany(Employee.self)
    var id: Int64
}

// The employee info
struct EmployeeInfo {
    var employee: Employee
    var company: Company?
    var employeeCount: Int
}

func fetchEmployeeInfos(_ db: Database) throws -> [EmployeeInfo] {
    // 1. Fetch employees with their company
    struct EmployeeAndCompany: Decodable, FetchableRecord {
        var employee: Employee
        var company: Company?
    }
    let employeeAndCompanies = try Employee
        .including(optional: Employee.company)
        .asRequest(of: EmployeeAndCompany.self)
        .fetchAll(db)
    
    // 2. Fetch the counts for those companies
    struct CompanyCount: Decodable, FetchableRecord {
        var id: Int64
        var employeeCount: Int
    }
    
    let companyIds = Set(employeeAndCompanies.compactMap { $0.company?.id })
    let companyCounts = try Company
        .filter(ids: companyIds)
        .select(Column("id"))
        .annotated(with: Company.employees.count)
        .asRequest(of: CompanyCount.self)
        .fetchCursor(db)
    let countByCompanyId = try Dictionary(uniqueKeysWithValues: companyCounts.map {
        ($0.id, $0.employeeCount)
    })
    
    // 3. Merge both results together
    return employeeAndCompanies.map {
        let employeeCount = if let company = $0.company {
            countByCompanyId[company.id, default: 0]
        } else {
            0
        }
        
        return EmployeeInfo(
            employee: $0.employee,
            company: $0.company,
            employeeCount: employeeCount)
    }
}

// Usage
try DatabaseQueue().write { db in
    try db.create(table: "company") { t in
        t.autoIncrementedPrimaryKey("id")
    }
    try db.create(table: "employee") { t in
        t.autoIncrementedPrimaryKey("id")
        t.belongsTo("company")
    }
    
    try Company(id: 1).insert(db)
    try Employee(id: 1, companyId: 1).insert(db)
    try Employee(id: 2, companyId: 1).insert(db)
    
    try Company(id: 2).insert(db)
    try Employee(id: 3, companyId: 2).insert(db)
    
    try Employee(id: 4, companyId: nil).insert(db)
    
    let employeeInfos = try fetchEmployeeInfos(db)
    print(employeeInfos)
}

[pedrommcarrasco]

Got it, thank you 👍