Realm Subquery to filter data from 1 table w.r.t table 2

Question

Suppose that I have two models and corresponding tables in the Realm Database

public class Customer :Object {
   dynamic var  Id : String = ""
   dynamic var  NAME : String = ""
   dynamic var  Address : String = ""

   override public class func primaryKey() -> String? {
      return "Id"
   }  
}


public class Bills :Object {
    dynamic var  Id : String = ""
    dynamic var  Amount : String = ""
    dynamic var  CustomerId : String = ""

    override public class func primaryKey() -> String? {
        return "Id"
    }
}

What I am doing: I am getting list of All customers easily by doing this

realmObj.objects(Customer.self)

What I want: I want to do followings.

I want to get all the list of Customers but I also want that the list contain such customer on top who purchases items more often. I mean I want the list ordering on the basis of customers that purchases more time from our shop. For this I can get it from the customer Id in Bills tables. But I do not know how to do it in Realm.

I know it can be done through subquery but I am not getting how to do it in Realm. Please tell me what will be query/Predicate here to get the desired results.

Answer 1

I have two answers but there are a couple of things to address first.

For both of your classes, if you want them to be managed by Realm you need to include @objc before each var you want managed

public class Customer :Object {
   @objc dynamic var  Id : String = ""

or optionally add @objcMembers to the class name

@objcMembers class Customer :Object {
       dynamic var  Id : String = ""

The other thing is that class properties (vars) should always be lower case, class names should start with upper case. Nothing should be all CAPS.

public class Customer :Object {
   @objc dynamic var  customer_id : String = ""
   @objc dynamic var  name : String = ""

First solution is using your current structure:

var topCustomers = [(String, Int)]() //stores the customer id and count in an array of tuples
let results = realm.objects(Bills.self) //get all the bills
let allCustomerIds = results.map{ $0.CustomerId } //get a list of all of the customer id's
let uniqueIds = Set(allCustomerIds) //create a set of unique customer ids

for custId in uniqueIds { //iterate over the array of unique customer id's
    let count = results.filter("CustomerId == %@", custId).count // get a count of each customer id from results
    topCustomers.append( (custId, count) ) //add the customer id and it's count to the array
}

topCustomers.sort { $0.1 > $1.1 } //sort the array by count
for x in topCustomers { //print out the array - the customer with the most bills will be at the top
    print(x.0, x.1)
}

a second, more elegant approach uses a relationship between customers and their bills. This will provide a LOT more flexibility for generating reports, queries and overall organization.

Here's the updated classes:

class CustomerClass: Object {
   @objc dynamic var  customer_id = UUID().uuidString
   @objc dynamic var  name = ""
   @objc dynamic var  address = ""

   let billList = List<BillClass>()

   override public class func primaryKey() -> String? {
      return "customer_id"
   }
}

class BillClass: Object {
    @objc dynamic var  bill_id = UUID().uuidString
    @objc dynamic var  amount = ""

    override public class func primaryKey() -> String? {
        return "bill_id"
    }
}

and then some very short code to accomplish the same thing as in the first example

let customers = realm.objects(CustomerClass.self) //get all customers
let results = customers.map { ($0.name, $0.billList.count) } //iterate over all, creating tuple with customer name & bill count
let sortedResults = results.sorted(by: { $0.1 > $1.1} ) //sort by bill count, descending
sortedResults.forEach { print($0) } //print the results, customer will most bills at top

Notes the use of UUID().uuidString

@objc dynamic var  bill_id = UUID().uuidString

creates unique primary keys for your objects. Eliminates dealing with indexing, uniqueness, incrementing etc.