Need help performing a complex CoreData task

Question

This would be hard with pure SQL and I'm REALLY not sure how to do it with CoreData. Is it possible to come up with a predicate or do I need to do this programmatically?

Suppose (using the textbook example) I have a database of employees, some of whom are managers and some not. I want a list of managers who have an employee named "John" (or whatever) in their department. No manager will ever be named "John."

In SQL terms I'd start with this query:

SELECT * FROM EMPLOYEES WHERE NAME IS LIKE "JOHN*"

Then for each record in that table I'd say:

SELECT * FROM EMPLOYEES WHERE ROLE = "MANAGER" AND DEPARTMENT = (current department)

I think this can be done in a single SQL query though not sure how -- but in any case I am looking for a CoreData solution.

Thank you!

Answer 1

In core-data (not writting exact syntax) follow these steps:

1. fetch array of depts with employee name with john.
2. fetch employees with role as manager and department exists in array returned earlier.

If you are looking for exact syntax let me know.

Following is Single DB Query. This query will search for employee with role manager and department same as department on employee with john in his start of name.

SELECT * 
FROM EMPLOYEE 
WHERE ROLE = MANAGER 
      AND DEPARTMENT IN (SELECT DEPARTMENT 
                         FROM EMPLOYEE 
                         WHERE NAME IS LIKE "JOHN*")