Nspredicate with Subquery

NSPredicate with SubQuery

This can be done with a SUBQUERY clause. If myPlayer is the player in question:

let predicate = NSPredicate(format:"SUBQUERY(games,$g, $g.player == %@).@count == 0", myPlayer)

Quick Explanation of SUBQUERY in NSPredicate Expression

This is what a subquery evaluates to. (Found from this mailing list thread, the #1 hit for “NSPredicate subquery” in Google.) That bit of documentation also explains how the predicate format string syntax relates to it.

NSPredicate Inside a SUBQUERY

Thanks to some pointers from @Willeke I was able to come up with a solution.

public extension NSPredicate{
    public func stringForSubQuery(prefix:String) -> String{
        var predicateString = ""
        if let predicate = self as? NSCompoundPredicate{
            for (index, subPredicate) in predicate.subpredicates.enumerate(){

                if let subPredicate = subPredicate as? NSComparisonPredicate{
                    predicateString = "\(predicateString) \(prefix)\(subPredicate.predicateFormat)"
                }
                else if let subPredicate = subPredicate as? NSCompoundPredicate{
                    predicateString = "\(predicateString) (\(subPredicate.stringForSubQuery(prefix)))"
                }
                //if its not the last predicate then append the operator string
                if index < predicate.subpredicates.count - 1 {
                    predicateString = "\(predicateString) \(getPredicateOperatorString(predicate.compoundPredicateType))"
                }
            }
        }
        return predicateString
    }
    private func getPredicateOperatorString(predicateType: NSCompoundPredicateType) -> String{
        switch(predicateType){
        case .NotPredicateType: return "!"
        case .AndPredicateType: return "&&"
        case .OrPredicateType: return "||"
        }
    }
}

And here is the usage

let ordersPredicate = NSPredicate()//some predicate passed in
let ordersPredicateFormat = orderPredicate.stringForSubQuery("$x.")
let subQueryPredicate = NSPredicate(format: "SUBQUERY(orders, $x, \(ordersPredicateFormat)).@count > 0")

NSPredicate SUBQUERY aggregates

1. Yes, think of nested subqueries. See Dave DeLong's answer that explains subquery in very simple terms.
2. The reason your @avg does not work is unknown because it should actually work on any collection that has the appropriate attributes required by the aggregate function.
3. See 1.: SUBQUERY returns a collection.

Here is the transcript of an experiment that proves that the subquery works as expected.

import UIKit
import CoreData

class Department: NSManagedObject {
    var name = "Department"
    var employees = Set<Person>()

    convenience init(name: String) {
        self.init()
        self.name = name
    }
}

class Person: NSManagedObject {
    var name: String = "Smith"
    var salary: NSNumber = 0

    convenience init(name: String, salary: NSNumber) {
        self.init()
        self.name = name
        self.salary = salary
    }
}

let department = Department()
department.employees = Set ([
 Person(name: "Smith", salary: NSNumber(double: 30000)),
 Person(name: "Smith", salary: NSNumber(double: 60000)) ])

let predicate = NSPredicate(format: "SUBQUERY(employees, $e, $e.name = %@).@avg.salary > 44000", "Smith")

let depts = [department, Department()]
let filtered = (depts as NSArray).filteredArrayUsingPredicate(predicate)

The above returns exactly one department with the two employees. If I substitute 45000 in the predicate, the result will return nothing.

Swift NSPredicate SUBQUERY with NOT IN?

Super easy! Here's the format for a regular filter

If you have a DogClass

class DogClass: Object {
   @objc dynamic var name = ""
}

and then want a list of dogs that are not named Dino and Fido, here's the code

 let dogNames = ["Fido", "Dino"]
 let results = realm.objects(DogClass.self).filter("NOT dog_name IN %@", dogNames)

The result of a subquery is going to be dependent on what result you expect and what the subquery is for.

For example, let's say we have a PersonClass that has property of dogs, which is a list of dogs they know. If we want all persons that do not know Fido or Dino, this is the query

let personResults = realm.objects(PersonClass.self).filter("NOT ANY dogs.dog_name in %@", dogNames)

EDIT

Based on an updated question, let's try this. Since I used a PersonClass in the above I will pose this question.

I want a list of all of the people (Shelters) that do not have a breed (race)
of Hound. Here's the Breed class to track the breeds

class BreedClass: Object {
    @objc dynamic var breed = ""
}

and the DogClass that has a breed property (like 'Race' in the question)

class DogClass: Object {
    @objc dynamic var dog_id = NSUUID().uuidString
    @objc dynamic var dog_name = ""
    @objc dynamic var dog_breed: BreedClass?

    override static func primaryKey() -> String? {
        return "dog_id"
    }
}

and then finally the Person class that has a List of DogClass objects

class PersonClass: Object {
    @objc dynamic var person_id = UUID().uuidString
    @objc dynamic var first_name = ""

    let dogs = List<DogClass>()

    override static func primaryKey() -> String? {
        return "person_id"
    }
}

Then we have some populated breed objects

let b0 = BreedClass()
b0.breed = "Mut"

let b1 = BreedClass()
b1.breed = "Poodle"

let b2 = BreedClass()
b2.breed = "Hound"

and then add breeds to the dogs and add the dogs to the persons. In this case we're only going to have one dog that's a b2, Hound

let d2 = DogClass()
d2.dog_name = "Sasha"
d2.dog_breed = b2

In this case I added 4 people, Bert, Ernie, Grover and The Count. Ernie was the only person I added the hound to.

Finally a query that will return all people that do NOT have a breed of Hound.

let breed = "Hound"
let personResults = realm.objects(PersonClass.self).filter("NOT ANY dogs.dog_breed.breed == %@", breed)
for person in personResults {
    print(person.first_name)
}

and the output

Bert
Grover
The Count

And Ernie is missing because he has a Hound.

NSPredicate subquery syntax

Man, "unfriendly" is an understatement on that array!

OK, I think I figured this out:

NSArray *dataRows = @[
                      @{ @"row" : @"1",
                         @"row_values" : @[
                                           @{ @"property_id" : @"47cc67093475061e01000542", 
                                              @"property_value" : @"Mr." },
                                           @{ @"property_id" : @"47cc67093475061e01000540", 
                                              @"property_value" : @"Male" }
                                          ]
                       },
                      @{ @"row" : @"2",
                         @"row_values" : @[
                                           @{ @"property_id" : @"47cc67093475061e01000542", 
                                              @"property_value" : @"Ms." },
                                           @{ @"property_id" : @"47cc67093475061e01000540", 
                                              @"property_value" : @"Female" }
                                          ]
                       }
                     ];

NSPredicate *p = [NSPredicate predicateWithFormat:@"SUBQUERY(row_values, $rv, $rv.property_id = %@ AND $rv.property_value = %@).@count > 0", @"47cc67093475061e01000540", @"Male"];

NSArray *filtered = [dataRows filteredArrayUsingPredicate:p];

So let's see what this predicate is doing.

1. Start with the outer-most level:

   SUBQUERY([stuff]).@count > 0
   

   A SUBQUERY returns an array of objects. We're going to run this SUBQUERY on every NSDictionary in the dataRows array, and we want to aggregate all of the dictionaries where the SUBQUERY on that dictionary returns something. So we run the SUBQUERY, and then (since it returns a collection), ask it for how many items were in it (.@count) and see if that's greater than 0. If it is, then the top-level dictionary will be in the final filtered array.

2. Dig in to the SUBQUERY:

   SUBQUERY(row_values, $rv, $rv.property_id = %@ AND $rv.property_value = %@)
   

   There are three parameters to every SUBQUERY: A key path, a variable, and a predicate. The key path is the property of the object that we're going to be iterating. Since the SUBQUERY is being evaluated on the outer-most dictionaries, we're going to ask for the @"row_values" of that dictionary and get back an array. The SUBQUERY will then iterate over the items in the row_values collection.

   The variable is what we're going to call each item in the collection. In this case, it's simply going to be $rv (shorthand for "row value"). In our case, each $rv will be an NSDictionary, since the row_values "property" is an array of dictionaries.

   Finally, the predicate is going to be executed, with the $rv getting replaced for each dictionary in turn. In this case, we want to see if the dictionary has a certain property_id and a certain property_value. If it does, it will be aggregated into a new array, and that is the array that will be returned from the SUBQUERY.

   So to say it a different way, the SUBQUERY is going to build an array of all the row_values that have a property_id and property_value of what we're looking for.

And finally, when I run this code, I get:

(
        {
        row = 1;
        "row_values" =         (
                        {
                "property_id" = 47cc67093475061e01000542;
                "property_value" = "Mr.";
            },
                        {
                "property_id" = 47cc67093475061e01000540;
                "property_value" = Male;
            }
        );
    }
)

NSPredicate with nested subqueries failing to compile(core data)

Mixing stringWithFormat and predicateWithFormat is error-prone because the quoting
and escaping rules are different. This might work (untested!):

[NSPredicate predicateWithFormat:@"SUBQUERY(subcategories, $x, SUBQUERY($x.gyms, $y, ANY $y.programs.uid IN %@).@count > 0).@count > 0", activeProgramsUIDsArray];

NSExpression respect a subquery NSPredicate

I dont have anything to back my own answer, but after struggling with this for hours. I thought that its not that the NSExpression not respecting the fetchRequest's NSPredicate but its more to the fact that the predicate of the fetchRequest is a subquery.

SELECT t0.ZPERSONID, t0.ZNAME, 
    (SELECT TOTAL(t2.ZAMOUNT) FROM ZTRANSACTION t2 WHERE (t0.Z_PK = t2.ZPERSON) ) 
FROM ZPERSON t0 
WHERE 
     (SELECT COUNT(t1.Z_PK) FROM ZTRANSACTION t1 WHERE (t0.Z_PK = t1.ZPERSON AND (( t1.ZDATE >= ? AND  t1.ZDATE <= ?))) ) > ? 

So, the subquery in the predicate is not really transformed into the WHERE clause I want. I think if it was creating a JOIN instead, that'd work.

My solution in the end is to work the other way around. If I create the fetchRequest from Transaction instead, that work perfectly.

NSExpression *amountKeyPath = [NSExpression expressionForKeyPath:@"amount"];
NSExpression *sumAmountExpression = [NSExpression expressionForFunction:@"sum:" arguments:@[amountKeyPath]];

// Create the expression description for that expression.
NSExpressionDescription *description = [[NSExpressionDescription alloc] init];
[description setName:@"sum"];
[description setExpression:sumAmountExpression];
[description setExpressionResultType:NSDecimalAttributeType];

// Create the sum amount fetch request,
self.sumAmountFetchRequest = [NSFetchRequest fetchRequestWithEntityName:@"Transaction"];
self.sumAmountFetchRequest.resultType = NSDictionaryResultType;
self.sumAmountFetchRequest.predicate = [NSPredicate predicateWithFormat:@"date >= %@ AND date <= %@", self.startDate, self.endDate];
self.sumAmountFetchRequest.propertiesToFetch = @[@"person.name", description];
self.sumAmountFetchRequest.propertiesToGroupBy = @[@"person.name"];

That works perfectly. It has to be grouped by the "person.name" so that it would use the sum: as wanted.

The SQL generated would be

SELECT  t1.ZPERSONID, total( t0.ZAMOUNT) 
FROM ZTRANSACTION t0 
      LEFT OUTER JOIN ZPERSON t1 ON t0.ZPERSON = t1.Z_PK 
WHERE ( t0.ZDATE >= ? AND  t0.ZDATE <= ?) GROUP BY  t1.ZPERSONID 

Cheers,

---

Related Topics