Core Data SUBQUERY Syntax Issue

Question

I'm trying to build a fetch request with a predicate that walks some relationships in core data using a subquery. My schema looks like this:

All the information I have is the ChargeCodeType and the Employee. What I'm trying to do is get all the TimeEntries that belong to the Employee that I ahve and sum up the hours column in TimeEntry. I have a working subquery that is of this format:

static func fetchRequestForChargeCodeTypeWithEmployeeNumber(context : NSManagedObjectContext, chargeCodeType : String, employeeId : Int) -> NSFetchRequest{
        let entity = NSEntityDescription.entityForName("ChargeCodeType", inManagedObjectContext: context)
        let fetchRequest = NSFetchRequest()
        fetchRequest.entity = entity

        let typePredicate = NSPredicate(format: "type == %@ AND SUBQUERY(chargeCodes, $t, ANY $t.timeEntries.person.employeeID == %@).@count != 0", chargeCodeType, NSNumber(integer:employeeId))
        fetchRequest.predicate = typePredicate

        return fetchRequest
    }

However I believe the problem here is the 'ANY' keyword and what is happening is if I have multiple Employees when I sum up the charge code time entries, I'm getting time entries back for multiple users when I sum up the time entries on a charge code. Here is more relevant code showing how I use the predicate.

 func constructDataSourceYearToDateUtilization(chargeCodeType : String){

        let chargeCodeTypeFetchRequest = CoreDataUtilities.fetchRequestForChargeCodeTypeWithEmployeeNumber(coreDataStack.managedObjectContext, chargeCodeType: chargeCodeType, employeeId: employee.employeeID!.integerValue)
        chargeCodeTableDataSource = [YTDChargeCodeModel]()
        do {
            if let chargecodeType = try coreDataStack.managedObjectContext.executeFetchRequest(chargeCodeTypeFetchRequest).first as? ChargeCodeType {

                let chargeCodes = chargecodeType.chargeCodes?.allObjects as? [ChargeCode]

                yearToDateHoursSum = 0

                for chargeCode in chargeCodes! {

                    let ytdChargeCodeModel = YTDChargeCodeModel()
                    ytdChargeCodeModel.chargeCode = chargeCode
                    ytdChargeCodeModel.fullChargeCode = chargeCode.chargeCode
                    ytdChargeCodeModel.chargeCodeDescription = chargeCode.chargeCodeDescription
                    ytdChargeCodeModel.totalHours = chargeCode.valueForKeyPath("timeEntries.@sum.hours") as! Int
                    yearToDateHoursSum = yearToDateHoursSum + ytdChargeCodeModel.totalHours
                    chargeCodeTableDataSource.append(ytdChargeCodeModel)
                }

                totalHoursLabel.text = "\(yearToDateHoursSum)hrs"
                //Sort by charge code name.
                chargeCodeTableDataSource.sortInPlace { $0.fullChargeCode!.compare($1.fullChargeCode!) == .OrderedAscending }

            } else {
                print("Could not get the charge code type")
                totalHoursLabel.text = "0hrs"
            }
        } catch let error as NSError {
            print("Hit error when fetching user profile with error message: \(error.localizedDescription)")
        }
        tableView.reloadData()
    }

Is there any way to make this work with a subquery or should I just re-write the approach?

Answer 1

The problem is not in your predicate, but in this line:

ytdChargeCodeModel.totalHours = chargeCode.valueForKeyPath("timeEntries.@sum.hours") as! Int

The @sum includes ALL the timeEntries for the relevant chargeCode, regardless of the employee. A quick fix would be to loop through all the timeEntries and only add those where the person.employeeID matches (or equivalently to filter the chargeCode.timeEntries to a temporary set, and use @sum on that).

Also you could simplify a little by fetching ChargeCodes using a predicate like this:

let typePredicate = NSPredicate(format: "chargeCodeType.type == %@ AND (ANY timeEntries.person.employeeID == %@)", chargeCodeType, NSNumber(integer:employeeId))

rather than fetching ChargeCodeTypes.