Help with CoreData Query

Question

I am looking for ways to write some basic queries in CoreData and there are not examples in the documentation. Following is my query:

1. I have an Expense object and it has an expenseAmount field.
2. Expense can be linked to an ExpenseCategory object.
3. ExpenseCategory may just define the category for the expense (for eg Food) or it may have a unit rate (for eg Mileage). If it is just name, the expense value is the expenseAmount in Expense object else it is expenseAmount * unitRate in ExpenseCategory.
4. Since Expense to Category link is optional, the final expense would be based on presence/absence of Category and the unit rate.

So a SQL query to calculate total expenses would be :

select
    TOTAL(e.amount * IFNULL(c.rate, 1))
from EXPENSE e
LEFT OUTER join CATEGORY c on
    e.category = c.id

How can this be done in CoreData?

Answer 1

A simpler solution might be to implement another method on your Expense class that gives you the appropriately calculated amount.

E.g.

- (NSDecimalNumber *) calculatedExpenseAmount {

  NSDecimalNumber *actualAmount = self.expenseAmount;

  // Pseudo-code begins
  if (self.expenseCategory != nil) {
    actualAmount = self.expenseAmount * self.expenseCategory.unitRate;
  }

  return actualAmount;
}

---

I'm adding on to my previous answer.

If you want to avoid pulling in every managed object, you could use the NSDictionary-result query to just pull out the expenseAmount and expenseCategory.unitRate values.

- (NSDecimalNumber *) totalExpenses
{
    // Fetch all of the expense amounts and unit rate of any related category.

    NSFetchRequest *request = ...;
    [request setManagedObjectContext:<...>];
    [request setEntity:<ExpenseAccountDescription>];
    [request setResultType:NSDictionaryResultType];
    NSArray *props = [NSArray arrayWithObjects:@"expenseAmount", @"category.unitRate", nil];
    [request setPropertiesToFetch:props];

    NSArray *amounts = [request executeRequest:...];
    // amounts is an array of dictionaries, each hold the desired property values.

    // Loop and sum the individual amounts

    NSDecimal *total = [[NSDecimalNumber zero] decimalNumber];
    NSAutoreleasePool *pool = nil; // contain the mess

    NSCalculationError err = NSCalculationNoError;

    for (NSDictionary *result in amounts) 
    {
        pool = [NSAutoreleasePool new];

        NSDecimal newTotal = [[NSDecimalNumber zero] decimalNumber];
        NSDecimalNumber *expenseAmount = [result valueForKeyPath:@"expenseAmount"];
        NSDecimalNumber *unitRate = [result valueForKeyPath:@"category.unitRate"];

        if (unitRate != nil) {
            // do the unit rate multiplication and accumulate the result in the total

            NSDecimal calculated = [[NSDecimalNumber zero] decimalNumber];
            err = NSDecimalMultiply (&calculated, [expenseAmount decimalNumber], [unitRate decimalNumber], NSRoundBankers);
            if (err == NSCalculationNoError) {
                err = NSDecimalAdd (&newTotal, total, calculated, NSRoundBankers);
            }
        }
        else {
            // just accumulate the result in the total

            err = NSDecimalAdd (&newTotal, total, [expenseAmount decimalNumber], NSRoundBankers);
        }

        // Keep the new total
        NSDecimalCopy(&total, newTotal);

        [pool drain];
    }

    return [NSDecimalNumber decimalNumberWithDecimal:total];
}

If you have 10000 expense entries, this fetch and calculation might take less than 1MB of RAM. Instruments would be your friend in measuring that.