1

I have 2 entities: Invoice and Product, that are related with a "to-many" relationship of 'products' from Invoice (Invoice.products is a list of Product entities for that invoice).

I'm trying to use an aggregate query to retrieve the # of times a given product has been invoiced on any invoice. So let's say I have 2 invoices:

Invoice 1

  • Product 1

  • Product 2

Invoice 2

  • Product 2

  • Product 3

And I want a count of the # of times Product 1 has been invoiced. In this case, 1. # of times Product 2 has been invoiced? 2.

If I query for the count: of the number of times Product 1 shows up, it returns 2, since there are 2 products for Invoice 1, and it should be 1. It seems in the following code, the predicate filters for any "Invoice" entity that has product 1, and once it finds one, it counts up the # of products, REGARDLESS OF THE PRODUCT ID:

//Create an aggregate query on Invoice

//create the NSExpression to tell our NSExpressionDescription which attribute we are performing the calculation on
NSExpression *keyExpression = [NSExpression expressionForKeyPath:@"products.quantity"];

//create the NSExpression to tell our NSExpressionDescription which calculation we are performing.
NSExpression *maxExpression = [NSExpression expressionForFunction:@"count:" arguments:[NSArray arrayWithObject:keyExpression]];

NSExpressionDescription *description = [[NSExpressionDescription alloc] init];
[description setName:@"quantityCount"];
[description setExpression:maxExpression];
[description setExpressionResultType:NSDoubleAttributeType];


MyAppDelegate *appDelegate = (MyAppDelegate *)[[UIApplication sharedApplication] delegate];
NSManagedObjectContext *context = [appDelegate getManagedObjectContext];


NSFetchRequest *request = [[NSFetchRequest alloc] initWithEntityName:@"Invoice"];
[request setPredicate:[NSPredicate predicateWithFormat:@"(deletedDate == nil) AND (products.id CONTAINS %@)", product.id]];

NSError *error;
[request setPropertiesToFetch:[NSArray arrayWithObject:description]];
[request setResultType:NSDictionaryResultType];

NSArray *results = [context executeFetchRequest:request error:&error];
if (results != nil && results.count > 0)
{
    NSDecimalNumber *quantityCount = [[results objectAtIndex:0] valueForKeyPath:@"quantityCount"];
    NSLog(@"The count for this product is: %@", quantityCount);
}

My Question is, how do I perform an aggregate query that ONLY counts the # of Product 1s? I feel like I need to add criteria to my fetch request in a different way or in another place. Any help would be appreciated!

Here are links to what my data model looks like inside xcode:

Invoice

Product

Jeff
  • 277
  • 2
  • 6

1 Answers1

0

You could simply use the inverse (to-many) relationship from Product to Invoice. Assuming that the inverse relationship is called "invoices":

Product *product = ... ; // the product for which you need the # of invoices
NSUInteger *count = [product.invoices count];
Martin R
  • 529,903
  • 94
  • 1,240
  • 1,382
  • The inverse relationship is a to-one relationship of 'invoice'. In other words, the inverse relationship from the product entry to the invoice is a 1 to 1 mapping from the specific instance of the product back to the specific invoice that the product is on. I'm really looking for a core data fetch request solution. – Jeff Jun 20 '14 at 21:24
  • @Jeff: If a product can be related to many invoices (such as in your example, Project2 is related to Invoice1 and Invoice2) then there should be a to-many relationship from Product to Invoice. – Martin R Jun 21 '14 at 05:40
  • Changing the designated relationship to 'to Many' doesn't seem to affect my ability to query the data store differently. In traditional SQL, to represent the relationship for Invoice products, I'd have an invoiceproduct table with which I could easily query for an aggregation such as: select count(ip.quantity) as Count from invoiceproduct ip where productid='Product2'; – Jeff Jun 23 '14 at 17:07
  • Instead I get the following sql-lite query: SELECT DISTINCT (SELECT COUNT(t4.ZQUANTITY) FROM Z_2PRODUCTS t3 JOIN ZPRODUCT t4 ON t3.Z_4PRODUCTS = t4.Z_PK WHERE (t0.Z_PK = t3.Z_2INVOICE) ) FROM ZINVOICE t0 JOIN Z_2PRODUCTS t1 ON t0.Z_PK = t1.Z_2INVOICE JOIN ZPRODUCT t2 ON t1.Z_4PRODUCTS = t2.Z_PK WHERE ( NSCoreDataStringSearch( t2.ZID, ?, 0, 0)); returns Product2 has a count of 2! – Jeff Jun 23 '14 at 17:08
  • @Jeff: Perhaps I did not understand your question correctly. If `invoices` is the to-many relationship from "Product" to "Invoice", then `[product1.invoices count]` should return `1` and `[product2.invoices count]` should return `2`. No fetch request needed. Is that what you are looking for? – Martin R Jun 23 '14 at 17:16
  • I tried the [product.invoices count]; after a standard fetch request for the entity "Product", and it is always returning a count of 0 for me, even though the products are used in invoices. I think the problem with this might be I'm using the entity "Product" to do double duty: I have a "Product" table that lists all my products, and I reuse the exact same entity to describe the "products" applied to an invoice in the Invoice entity (relationship: products). So adding a product to an invoice is each a new Product object. Am I using core data wrong by setting it up this way? – Jeff Jun 29 '14 at 06:16
  • so the only way to get that list of products for an invoice, is to query for the list of products through the entity "Invoice" not "Product". Which is why I'm looking for a solution that somehow lets me query through Invoice. – Jeff Jun 29 '14 at 06:18
  • @Jeff: Sorry, but I am getting lost. Perhaps you can add a screenshot of the Core Data model to your question. – Martin R Jun 29 '14 at 10:33
  • I added links to images of my data model – Jeff Jul 02 '14 at 03:33