Friday, October 21, 2016

One-to-Many Relationship and Subquery

Recently I have to deal with a case in my app. In a one-to-many relationship, I need to define a predicate for many's element to be checked. Here taking Person as a simple class for example, as in following swift class:

  1. class Person {
  2.  var name: String?
  3.  var birthYear: Int?
  4.  var friends: NSSet?
  5. }

the Person class has three properties, name, birthYear, and friends. I use CoreData model to define Person entity. What I need to find a person whose has friends(at least one) and those friends has to meet certain criteria.

For example, find all person who has friends and at least one of friends' birth year is greater than 2000.

Soon I found a solution: I need to build a predicate with function Subquery. For above example, my fetch request can be built in following codes:

  1. let request = NSFetchRequest<Person> = NSFetchRequest(entityName: "Person")
  2. let predicate = NSPredicate(format: "SUBQUERY(friends, $x, $x.name CONTAINS 'David' && $x.birthYear > 2000).@count > 0")
  3. request.predicate = predicate

Subquery Function


The Subquery function is rarely mentioned, however, it is a very powerful to resolve some complicated one-to-many relationship.

The function takes three parameters:

  1. A collection
  2. a variable, and
  3. a predicate

Basically, the function will loop through the collection to verify each element if the predicate condition is true, and all the matched elements will be returned back as a collection or empty if none met. For above example case, the Subquery will enumerate each person in friends collection, return a collection of persons whose name contains "David" and whose (David's) birth year is greater than 2000.

For this kind or more complicated relationships, this function is very concise and powerful. For example, relationship may go one more level deeper: friends' friends whose name contains "Lisa" and whose (Lisa's) birth year is greater than 2005. For this case, nested Subquery can be used. That is, a Subquery contains another Subquery to verify one more deep relationship:

  1. let request = NSFetchRequest<Person> = NSFetchRequest(entityName: "Person")
  2. let predicate = NSPredicate(format: "SUBQUERY(friends, $x,
  3.  SUBQUERY($x.friends, $y, $y.name CONTAINS 'Lisa' && $y.birthYear > 2005).@count > 0).@count > 0")
  4. request.predicate = predicate

The Subquery function can further use ALL, ANY or NONE operator in the third predicate parameter on collection property for more complicated predicate conditions.

  1. SUBQUERY(friends, $x, $x.friends != nil && ALL $x.friends.birthYear > 2000)
  2. SUBQUERY(friends, $x, $x.friends != nil && ANY $x.friends.birthYear > 2000)
  3. SUBQUERY(friends, $x, $x.friends != nil && NONE $x.friends.birthYear > 2000)

Operator ALL, ANY or NONE applies to the left collection for all of, at least one, or none of element in the collection. In this way, frinds.friends's property(birthYear) can be added to complete the predicate condition.

As you can see, in all above codes, Subquery is used in NSPredicate, which is attached to NSFetchRequest to fetch entity objects in CoreData context. To test Subquery in various cases, I have to find some way to add objects to CoreData database. In most cases I had to run my app to do my tests. That's very time consuming and painful process.

Further investigating into Subquery, I found that Subquery used in NSPredicate is actually based on NSExpression. Actually, Subquery can be tested by using NSExpression with various samples of collections. My next blog will discuss this very interesting topic.


References



0 comments: