How to setup NSPredicate and NSEntityDescription in Coredata to do complicated SQL query

Question

I know how to create NSPredicate to do sql like "SELECT * FROM DRINK". But how about this query:

"SELECT I.name, D_I.amount 
FROM DRINK_INGREDIENT D_I, DRINK, INGREDIENT I 
WHERE D_I.drinkID=1 AND DRINK.drinkID=1 AND I.ingredientID = D_I.ingredientID;"  

How do I even set the NSEntityDescription, and NSPredicate for this kind of query?

Answer 1

Typically with Core Data you will design and use a data model that meets your needs. You should not think about it in terms of SQL--it does not even have to use SQL for storage--nor should you try to directly translate a SQL query into a fetch request with a predicate, etc.

That said, it can sometimes be helpful to think in terms of SQL WHERE clauses when building predicates, but really fetch requests comes down to what entity you need to get and how the collection should be filtered and/or sorted, etc.

Fetch requests are limited to a single entity, so I think you would need multiple fetch requests to simulate your query.

What does your Core Data data model look like? What are you trying to accomplish? What have you tried so far?

UPDATE
It sounds like your data model includes two entities: Drink and Ingredient with a many-to-many relationship between them:

Drink <<-->> Ingredient

Note that in Core Data, there is no DrinkIngredient entity unless you explicitly create it (there is an additional table for the many-to-many relationship, but it's abstracted away from you). Since you want an amount value associated with the rows in the additional table, I would recommend adding a DrinkIngredient entity in Core Data:

Drink <-->> DrinkIngredient <<--> Ingredient

Note: a DrinkIngredient has exactly one Drink and one Ingredient. Drinks can have many DrinkIngredients and Ingredients can be used by many DrinkIngredients.

It sounds like you want to get the name and amount for the list of ingredients for a particular drink. To do this, simply fetch DrinkIngredient objects with a filter predicate like this:

// assuming "aDrink" is a reference to a particular Drink object
// and "drink" is the relationship from DrinkIngredient to Drink
fetchRequest.predicate = [NSPredicate predicateWithFormat:@"drink == %@",aDrink];

// if the fetch request result array is named "ingredientList"
// and the relationship from DrinkIngredient to Ingredient is "ingredient"
for (DrinkIngredient *di in ingredientList) {
    NSString *ingredientName = di.ingredient.name;
    NSUInteger amount = di.amount.integerValue;

    // use "ingredientName" and "amount" here
}

Since you are using Core Data and not SQL, you do things differently. For example, if you wanted to display an ingredient list with name and amount for all drinks, you would simply fetch all Drink objects (no filter predicate) and you access the ingredients through the relationship from Drink to DrinkIngredient.

Again, you should think about what you are trying to accomplish and design and use your data model appropriately. You should not think about SQL or queries.